Multiple Counts with Different Conditions in single MySQL query

You can easily get multiple counts with different conditions or criteria in a single query in MySQL, using IF or CASE statements. In this article, we will look at how to get multiple counts with multiple conditions in MySQL.

Multiple Counts with Different Conditions in single MySQL query

Here are the steps to get multiple counts with different conditions in single MySQL query.

Let us say you have the following table product_sales(id, product, order_date, amount)

mysql> create table product_sales(
     id int,
     product varchar(10),
     order_date date,
     amount int
     );

mysql> insert into product_sales(id, product, order_date, amount)
     values(1, 'A', '2021-01-01', 250),
     (2, 'B', '2021-01-02', 200),
     (3, 'A', '2021-01-03', 150),
     (4, 'B', '2021-01-04', 250);

mysql> select * from product_sales;
+------+---------+------------+--------+
| id   | product | order_date | amount |
+------+---------+------------+--------+
|    1 | A       | 2021-01-01 |    250 |
|    2 | B       | 2021-01-02 |    200 |
|    3 | A       | 2021-01-03 |    150 |
|    4 | B       | 2021-01-04 |    250 |
+------+---------+------------+--------+

Also read : How to Use CASE statement in MySQL

Let us say you want total count, count of product A orders, and count of product B orders with amount > 200 in single query.

Here is the SQL query to accomplish the above.

mysql> select count(*) as total_count,
        count(if(product='A',1,null)) as A_count,
        count(if(product='B' and amount>200,1,null)) as B_count
        from product_sales;
+-------------+---------+---------+
| total_count | A_count | B_count |
+-------------+---------+---------+
|           4 |       2 |       1 |
+-------------+---------+---------+

Let us look at the above query in detail.

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. Else it is not counted (assigned as NULL).

Please note, it is important to use null in case IF condition fails else even non-matching rows are counted.

Similarly, we calculate count for rows where product=B and amount > 200.

You can also get above result using CASE statement as shown below.

mysql> select count(*) as total_count,
 count(case when product='A' then 1 else null end) as A_count,
 count(case when product='B' and amount>200 then 1 else null end) as B_count
 from product_sales;
+-------------+---------+---------+
| total_count | A_count | B_count |
+-------------+---------+---------+
|           4 |       2 |       1 |
+-------------+---------+---------+

Bonus Read : How to Use COALESCE in MySQL

Get Multiple Counts in Ubiq

Ubiq Reporting tool supports all the above SQL queries and makes it easy to visualize SQL results in different ways. It also allows you to create dashboards & charts from MySQL data. Here is the SQL query mentioned above, in Ubiq.