How to Accurately Count Approved Comments in MySQL Using SUM()?

Counting with IF Condition in MySQL Query

Your objective is to retrieve the count of approved comments for news articles. However, your current query is returning a minimum value of 1 for the comments column, even if no comments exist for a particular article.

To resolve this issue, swap out the count() function with sum() in your query. Let's dive into the specifics:

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 

How it Works:

The sum() function evaluates every TRUE expression to 1 and FALSE expressions to 0, effectively returning the total number of approved comments.

This updated query now provides accurate counts, showing whether each news article has approved comments or not.

The above is the detailed content of How to Accurately Count Approved Comments in MySQL Using SUM()?. For more information, please follow other related articles on the PHP Chinese website!