How Can I Correctly Count Approved Comments in MySQL to Avoid a Minimum of 1?

Solving Comment Count Issue with IF Condition in MySQL Query

You're facing an issue where your MySQL query consistently returns a minimum comment count of 1 even in the absence of approved comments for certain news articles. To rectify this, let's explore a revised approach.

Your query employs the count() function to tally approved comments, but it inherently yields a minimum value of 1 for every row, irrespective of comment existence. The solution lies in utilizing the sum() function instead.

Consider the following modified 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

In this revised version, we utilize the sum() function within the if() statement's conditional expression. It calculates the aggregate count of 1's for approved comments and 0's for unapproved comments, effectively returning an accurate tally of approved comments for each news article.

The above is the detailed content of How Can I Correctly Count Approved Comments in MySQL to Avoid a Minimum of 1?. For more information, please follow other related articles on the PHP Chinese website!