Count with IF condition in MySQL query

Question

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.

What if you use SUM instead of COUNT ? – John Pick Mar 21 '12 at 05:14 — John Pick, Mar 21 '12 at 05:14

score 350 · Accepted Answer · edited Aug 07 '19 at 14:25

350

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

edited Aug 07 '19 at 14:25

ElChiniNet

2,778
2
19
27

answered Mar 21 '12 at 05:15

16

Or even SUM(ccc_news_comments.id = 'approved') as a MySQL-specific trick – mojuba Jan 21 '13 at 20:44
2

@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 Nov 09 '13 at 16:36
@mojuba [case and point](http://pastebin.com/JRqATRHB). `num_relevant_parts` is `SUM` with conditions, `num_total_parts` is `COUNT(parts.id)` (sorry for double comment, was too late to edit) – Robin Kanters Nov 09 '13 at 16:41

score 92 · Answer 2 · edited Jan 18 '15 at 23:48

92

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

edited Jan 18 '15 at 23:48

potashin

44,205
11
83
107

answered Jan 21 '13 at 20:42

mojuba

11,842
9
51
72

3

This makes for significantly more readable sql code. Beautiful solution. – Dag Sondre Hansen Jan 19 '15 at 12:25
Always finding this answer when searching for the problem, works as a charm with likely no downsides – John Oct 16 '20 at 02:51

score 40 · Answer 3 · edited Oct 26 '12 at 12:40

40

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.

edited Oct 26 '12 at 12:40

Alexis Pigeon

7,423
11
39
44

answered Oct 26 '12 at 12:21

Edemilson Lima

401
4
2

I think `count` is more intuitive than `sum` in this case. – Jeffery Jul 31 '17 at 02:40

score 5 · Answer 4 · answered Oct 11 '21 at 10:13

5

count(ccc_news_comments.id = 'approved' or null)

More concise

answered Oct 11 '21 at 10:13

Zhang Peng

51
1
1

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](/review/late-answers/30047999) – Smit Gajera Oct 11 '21 at 11:31

score 5 · Answer 5 · answered Mar 21 '12 at 05:16

5

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

answered Mar 21 '12 at 05:16

Mosty Mostacho

42,742
16
96
123

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 – Mar 21 '12 at 05:17
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 – Mosty Mostacho Mar 21 '12 at 05:19
@MostyMostacho, does `COALESCE` return the sum ? Any reference in MySQL doc ? – Istiaque Ahmed Nov 10 '17 at 10:41
Yes, why would't it? There are many references in the docs: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce – Mosty Mostacho Nov 10 '17 at 14:43

Count with IF condition in MySQL query

5 Answers5

Linked

Related