Count with IF condition in MySQL query

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


Use sum() in place of count()

Try below:

SELECT
    ccc_news . * , 
    SUM(if(ccc_news_comments.id = 'approved', 1, 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