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