MySQL: Multiple COUNT in one query with conditions

I’m still learning some tricks about MySQL as I go along and figured how to combine multiple COUNT queries into a single query returning one resultset.

I wanted to specifically count on the same column/field but each count having it’s own condition on the table and then group the results by a ‘created’ date column in order to get the records for the last month (30 days). You may want to do a count on different columns or even the same column but with different conditions.

To do this, you can use a condition inside the MySQL COUNT query itself using something like IF or a CASE statement. Here is a quick example:

SELECT Date(`created`)                       AS `date`,
       Count(IF(`column` = 'val1', 1, NULL)) AS `count1`,
       Count(IF(`column` = 'val2', 1, NULL)) AS `count2`
FROM   `table`
GROUP  BY Date(`created`)
ORDER  BY `created` ASC;

This will return a resultset with ‘date’, ‘count1′ and ‘count2′.

Thus ‘count1′ is the COUNT on the column where the value is something specific and same goes for ‘count2′, just a different value. You can use different operators here to determine what you want to count on. And like I mentioned earlier, you can use alternatives like CASE instead of IF when it is more suitable.

And of course you can use JOIN to include other tables into the query as needed.