I have two tables, one is for news and the other one is for comments and I want to get the count of the comments whose status has been set as approved.
SELECT
ccc_news . *,
count(if(ccc_news_comments.id = 'approved', ccc_news_comments.id, 0)) AS comments
FROM
ccc_news
LEFT JOIN
ccc_news_comments
ON ccc_news_comments.news_id = ccc_news.news_id
WHERE
`ccc_news`.`category` = 'news_layer2'
AND `ccc_news`.`status` = 'Active'
GROUP BY
ccc_news.news_id
ORDER BY
ccc_news.set_order ASC
LIMIT 20
But the problem with this query is that the minimum value that is fetched for the comments column is 1 whether there is any comment existent corresponding to that news or not.
Any help would be highly appreciable.
This question is related to
mysql
join
if-statement
count
Better still (or shorter anyway):
SUM(ccc_news_comments.id = 'approved')
This works since the Boolean type in MySQL is represented as INT
0
and 1
, just like in C. (May not be portable across DB systems though.)
As for COALESCE()
as mentioned in other answers, many language APIs automatically convert NULL
to ''
when fetching the value. For example with PHP's mysqli
interface it would be safe to run your query without COALESCE()
.
Replace this line:
count(if(ccc_news_comments.id = 'approved', ccc_news_comments.id, 0)) AS comments
With this one:
coalesce(sum(ccc_news_comments.id = 'approved'), 0) comments
This should work:
count(if(ccc_news_comments.id = 'approved', ccc_news_comments.id, NULL))
count()
only check if the value exists or not. 0 is equivalent to an existent value, so it counts one more, while NULL is like a non-existent value, so is not counted.
Source: Stackoverflow.com