Why Does My MySQL COUNT() Query Always Return at Least 1 Comment?

Counting Comments with MySQL's IF Condition

Problem:

You've crafted a query to count approved comments for news articles, but it always returns a minimum count of 1, even for articles without approved comments.

Solution:

To address this issue, replace the COUNT() function with the SUM() function in your query. SUM() accumulates values, ensuring an accurate count of approved comments.

Here's the revised query:

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

By using SUM(), the query now accurately calculates the count of approved comments associated with each news article.

The above is the detailed content of Why Does My MySQL COUNT() Query Always Return at Least 1 Comment?. For more information, please follow other related articles on the PHP Chinese website!