Count with IF condition in MySQL query
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
user1163513
Updated on July 08, 2022Comments
-
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 over 11 yearsWhat if you use SUM instead of COUNT ?
-
-
Admin over 11 yearscount(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 over 11 yearsSorry, 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 almost 11 yearsOr even SUM(ccc_news_comments.id = 'approved') as a MySQL-specific trick
-
Robin Kanters about 10 years@mojuba not 100% the same, your trick returns
null
whenCOUNT
(no conditions) would've returned0
. WhenCOUNT
would've returned anything but 0, but theSUM
does return 0, your trick returns0
. -
Robin Kanters about 10 years@mojuba case and point.
num_relevant_parts
isSUM
with conditions,num_total_parts
isCOUNT(parts.id)
(sorry for double comment, was too late to edit) -
Dag Sondre Hansen almost 9 yearsThis makes for significantly more readable sql code. Beautiful solution.
-
Jeffery over 6 yearsI think
count
is more intuitive thansum
in this case. -
Istiaque Ahmed about 6 years@MostyMostacho, does
COALESCE
return the sum ? Any reference in MySQL doc ? -
Mosty Mostacho about 6 yearsYes, why would't it? There are many references in the docs: dev.mysql.com/doc/refman/5.7/en/…
-
John about 3 yearsAlways finding this answer when searching for the problem, works as a charm with likely no downsides
-
Smit Gajera about 2 yearsWhile 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