How to Get Multiple Counts With Single Query in MySQL

Sometimes you may need to select multiple counts in one query, get multiple counts for different conditions, or select multiple counts with different criteria. In this article, we will look at how to get multiple counts with single query in MySQL.


How to Get Multiple Counts With Single Query in MySQL

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 Convert datetime to UTC

Let us say you want total count, count of product A orders, and count of product B orders 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',1,null)) as B_count
        from product_sales;
+-------------+---------+---------+
| total_count | A_count | B_count |
+-------------+---------+---------+
|           4 |       2 |       2 |
+-------------+---------+---------+

Also read : How to Group By Month in MySQL

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

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' then 1 else null end) as B_count
 from product_sales;
+-------------+---------+---------+
| total_count | A_count | B_count |
+-------------+---------+---------+
|           4 |       2 |       2 |
+-------------+---------+---------+

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!