Count with IF condition in MySQL query

258,347

Solution 1

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 

Solution 2

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

Solution 3

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.

Solution 4

Replace this line:

count(if(ccc_news_comments.id = 'approved', ccc_news_comments.id, 0)) AS comments

With this one:

coalesce(sum(ccc_news_comments.id = 'approved'), 0) comments
Share:
258,347
user1163513
Author by

user1163513

Updated on July 08, 2022

Comments

  • user1163513
    user1163513 over 1 year

    I have two tables, one is for news and the other one is for comments and I want to get the count of the comments whose status has been set as approved.

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

    But the problem with this query is that the minimum value that is fetched for the comments column is 1 whether there is any comment existent corresponding to that news or not.

    Any help would be highly appreciable.

    • John Pick
      John Pick over 11 years
      What if you use SUM instead of COUNT ?
  • Admin
    Admin over 11 years
    count(if(ccc_news_comments.id = 'approved', ccc_news_comments.id, 0)) ??? what will be meaning of using sum if you use ccc_news_comments.id
  • Mosty Mostacho
    Mosty Mostacho over 11 years
    Sorry, what do you mean? The boolean value becomes 0 or 1, then sum, and in case there is some null value coalesce with 0
  • mojuba
    mojuba almost 11 years
    Or even SUM(ccc_news_comments.id = 'approved') as a MySQL-specific trick
  • Robin Kanters
    Robin Kanters about 10 years
    @mojuba not 100% the same, your trick returns null when COUNT (no conditions) would've returned 0. When COUNT would've returned anything but 0, but the SUM does return 0, your trick returns 0.
  • Robin Kanters
    Robin Kanters about 10 years
    @mojuba case and point. num_relevant_parts is SUM with conditions, num_total_parts is COUNT(parts.id) (sorry for double comment, was too late to edit)
  • Dag Sondre Hansen
    Dag Sondre Hansen almost 9 years
    This makes for significantly more readable sql code. Beautiful solution.
  • Jeffery
    Jeffery over 6 years
    I think count is more intuitive than sum in this case.
  • Istiaque Ahmed
    Istiaque Ahmed about 6 years
    @MostyMostacho, does COALESCE return the sum ? Any reference in MySQL doc ?
  • Mosty Mostacho
    Mosty Mostacho about 6 years
    Yes, why would't it? There are many references in the docs: dev.mysql.com/doc/refman/5.7/en/…
  • John
    John about 3 years
    Always finding this answer when searching for the problem, works as a charm with likely no downsides
  • Smit Gajera
    Smit Gajera about 2 years
    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review