Count with IF condition in MySQL query

Use sum() in place of count()

Try below:

SELECT    ccc_news . * ,     SUM(if(ccc_news_comments.id = 'approved', 1, 0)) AS commentsFROM    ccc_news    LEFT JOIN        ccc_news_comments    ON        ccc_news_comments.news_id = ccc_news.news_idWHERE    `ccc_news`.`category` = 'news_layer2'    AND `ccc_news`.`status` = 'Active'GROUP BY    ccc_news.news_idORDER BY    ccc_news.set_order ASCLIMIT 20 

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().

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.