Need help adding a conditional COUNT() to a query already using JOINs

Question

Here's what I need to fetch:
- posts that have comments
- number of comments per post
- number of unread comments per post (expressed by a bool "read" column in the "comments" table)

The last part is what I'm having trouble with.
Here's my SQL so far:

SELECT
posts.id
, posts.title
, COUNT(comments.id) AS commentsCount
FROM posts
INNER JOIN comments
ON comments.postID = posts.id
GROUP BY
posts.id
ORDER BY
comments.createDate DESC

This works fine so far, but I need to COUNT() how many comments have their "read" field equal to 0. I'm not sure how to perform this additional COUNT() within the existing query.

Thanks in advance!

Answer 1

How about

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

Answer 2

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