Clever Geek Handbook

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


source to share


2 answers


What about



SUM(CASE comments.Read WHEN 0 THEN 1 ELSE 0 END)
+3


source 


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


source






More articles:


All Articles