SELECT [DISTINCT] COUNT([DISTINCT] IF(<condition>, <expression>, NULL)) AS alias_name FROM your_table_name; The syntax shows that: COUNT() function includes IF() function, which has a condition specified. If the <condition> is true, then the count will be calculated based on <expression> passed.
C) MySQL COUNT IF example You can use a control flow expression and functions e.g., IF , IFNULL , and CASE in the COUNT() function to count rows whose values match a condition. The IF() function returns 1 if the order's status is canceled, on hold or disputed, otherwise, it returns NULL .
The MySQL IF() function is used for validating a condition. The IF() function returns a value if the condition is TRUE and another value if the condition is FALSE. The MySQL IF() function can return values that can be either numeric or strings depending upon the context in which the function is used.
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
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()
.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With