How can I add an additional condition to this query (using CASE) ?

Question

Here's my query so far:

SELECT
posts.title
, SUM(CASE comments.status WHEN 'approved' THEN 1 END) AS commentsCount
FROM posts
INNER JOIN comments
ON comments.postID = posts.id
WHERE
posts.status = ?
GROUP BY
posts.title
ORDER BY
commentsCount DESC
LIMIT 5

I need to have it also check that comment.flagged = 0 when it gets commentsCount. I tried adding additional CASEs within the SUM() call, but this resulted in a fatal error. How can I achieve this?

Thanks!

Answer 1

SELECT
posts.title
, SUM(IF ((comments.status='approved' AND  comments.flagged = 0),1,0)) AS commentsCount
FROM posts
INNER JOIN comments
ON comments.postID = posts.id
WHERE
posts.status = ?
GROUP BY
posts.title
ORDER BY
commentsCount DESC
LIMIT 5

Answer 2

Seems like what you're really trying to do is this:

SELECT
posts.title
, COUNT(*) AS commentsCount
FROM posts
INNER JOIN comments
ON comments.postID = posts.id
AND comments.status = 'approved'
AND comments.flagged = 0
WHERE
posts.status = ?
GROUP BY
posts.title
ORDER BY
commentsCount DESC
LIMIT 5

Since you're only interested in comments whose status is 'approved', the condition should be in join condition.

Edit: I have updated the query assuming you want to count comments whose status is 'approved' and flagged is equal to 0.

Answer 3

Based off of Josh Davis' SQL, here's what worked for me:

SELECT
posts.title
, posts.slug
, COUNT(comments.id) AS commentsCount
FROM posts
INNER JOIN comments
ON comments.postID = posts.id
AND comments.status = 'approved'
AND comments.flagged = 0
WHERE
posts.status = ?
GROUP BY
posts.title
ORDER BY
commentsCount DESC
LIMIT 5