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!
