Need help adding a conditional COUNT () to a query already using JOINs
Here's what I need to do:
- posts that have comments
- number of comments per post
- number of unread comments per post (expressed by the bool "read" column in the "comments" table)
The last part is what I'm having trouble with.
Here is my SQL:
SELECT
posts.id
, posts.title
, COUNT (comments.id) AS commentsCount
FROM
INNER JOIN posts comments
ON comments.postID = posts.id
GROUP BY
posts.id
ORDER
comments.createDate DESC
So far this works, but I need to tell COUNT () how many comments the "read" field has equal to 0. I'm not sure how to execute this extra COUNT () within an existing query.
Thanks in advance!
+2
Steven mercatante
source
to share
2 answers
What about
SUM(CASE comments.Read WHEN 0 THEN 1 ELSE 0 END)
+3
Marc gravell
source
to share
SELECT
p.id
, p.title
, COUNT(c.id) AS commentsCount
, SUM(CASE c.IsRead WHEN 0 THEN 1 ELSE 0 END) AS commentsRead
, SUM(CASE c.IsRead WHEN 0 THEN 0 ELSE 1 END) AS commentsUnRead
FROM
posts p
INNER JOIN
comments c
ON
c.postID = p.id
GROUP BY
p.id
ORDER BY
c.createDate DESC
+2
RSolberg
source
to share