Menu
Is it possible in MySQL to do something like this:
SELECT COUNT(*) as totalcount,
COUNT(*) WHERE foo IS NULL as conditional_count
FROM baz
i.e. get two counts, one of everything, and one of things matching a WHERE clause, in a single select?
946
You can count multiple COUNT() for multiple conditions in a single query using GROUP BY. SELECT yourColumnName,COUNT(*) from yourTableName group by yourColumnName; To understand the above syntax, let us first create a table. The query to create a table is as follows.
The COUNT(*) function returns the number of rows in a result set returned by a SELECT statement. The COUNT(*) returns the number of rows including duplicate, non-NULL and NULL rows.
count(*) counts all rows in table to give total count. count(if(product='A',1,null)) as A_count – when we use an IF condition inside count function, it will only count rows where condition is true. Our condition is to match rows where product = A. So MySQL, counts only those rows where product is A.
This would work if your database supports the CASE WHEN statement, otherwise you'll still get the basic idea.
SELECT COUNT(*),
SUM(CASE WHEN FOO IS NULL THEN 1 ELSE 0 END) AS COUNT_CONDITIONAL
FROM baz
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
