MySQL database is commonly used to aggregate data, get summaries and statistics. If you need to get multiple counts from same table, then database developers tend to use separate queries for each count, especially if these counts are conditional. But did you know that you can get multiple counts in single query? In this article, we will learn how to multiple counts in single query.
Why Multiple Counts are Needed in Single Query
Sometimes database developers are required to get multiple counts from one or more tables. Some of these counts may depend on certain conditions while others may depend on some other conditions. Typically, database developers run separate queries to get each count. Here’s an example to illustrate it. Let us say you have the following sales table, and you want separate sum of orders where product is A and product is B
mysql> create table sales(id int, product varchar(255),orders int);
mysql> insert into sales(id,product,orders)
values(1, 'A',50),
(2,'B',60),
(3,'A',70),
(4,'B',40),
(5,'C',50);
mysql> select * from sales;
+------+---------+--------+
| id | product | orders |
+------+---------+--------+
| 1 | A | 50 |
| 2 | B | 60 |
| 3 | A | 70 |
| 4 | B | 40 |
| 5 | C | 50 |
+------+---------+--------+
Typically, you would use separate queries to get the above mentioned sums, as shown.
mysql> select sum(orders) from sales where product='A';
+-------------+
| sum(orders) |
+-------------+
| 120 |
+-------------+
mysql> select sum(orders) from sales where product='B';
+-------------+
| sum(orders) |
+-------------+
| 100 |
+-------------+
But if your tables are large with many columns and rows, then it can be time consuming to just run one query, let alone running multiple queries. In such cases, it is better to get all sums/counts using a single query.
How to Get Multiple Counts in Single Query
There are 3 main ways to get multiple counts in single query – using SUM with CASE, using COUNT with IF and using sub query. Let us look at each of them in detail. We will learn how to get multiple SUM in single query. If you want to get row count, you can use COUNT() function instead of SUM().
1. Using SUM with CASE
In this solution, we use conditional statements within a CASE statement to obtain multiple counts. This is a very efficient way to solve the problem, since it scans the table only once. Here is the query to get separate sums of orders where product is A and product is B.
mysql> SELECT sum(orders) as total,
SUM(CASE WHEN product = 'A' THEN orders ELSE 0 END) AS A_count,
SUM(CASE WHEN product = 'B' THEN orders ELSE 0 END) AS B_count
FROM sales;
+-------+---------+---------+
| total | A_count | B_count |
+-------+---------+---------+
| 270 | 120 | 100 |
+-------+---------+---------+
In the above query, we use two sum() functions, one each for product A and product B. In each sum() function, we use a CASE statement. For each row, the first CASE statement(used for 2nd column) results in orders column value if the product column value is A for that row, else it results in 0.
The second CASE statement(used for 3rd column) results in orders column value if product column value is B, else it results in 0. The sum() function adds up this value to calculate total sales where product is A and product is B.
If you want to get a row count instead of sum, then modify the query as shown. Instead of using orders column name in your query, use 1 and 0.
mysql> SELECT count(*) as total,
SUM(CASE WHEN product = 'A' THEN 1 ELSE 0 END) AS A_count,
SUM(CASE WHEN product = 'B' THEN 1 ELSE 0 END) AS B_count
FROM sales;
+-------+---------+---------+
| total | A_count | B_count |
+-------+---------+---------+
| 5 | 5 | 2 |
+-------+---------+---------+
2. Using COUNT with IF
This solution is similar to the above one. In this case, we use IF statement instead of using CASE. For each separate sum, we use IF() condition. If it evaluates to be true, then it returns one value, else it returns another value.
mysql> SELECT sum(orders) AS total,
sum(IF(product='A', orders, NULL)) AS A_count,
sum(IF(product='B', orders, NULL)) AS B_count
FROM sales;
+-------+---------+---------+
| total | A_count | B_count |
+-------+---------+---------+
| 270 | 120 | 100 |
+-------+---------+---------+
In the above query, for second column, we use if() function within sum() function. For each row, the IF condition results in orders column value if product=A, else it returns null.
mysql> SELECT count(*) AS total,
sum(IF(product='A', 1, NULL)) AS A_count,
sum(IF(product='B', 1, NULL)) AS B_count
FROM sales;
+-------+---------+---------+
| total | A_count | B_count |
+-------+---------+---------+
| 5 | 2 | 2 |
+-------+---------+---------+
Similarly, for third column, for each row, the if() function results in orders column value if product=B, else it returns null.
If you need to calculate row count instead of sum of orders column, then replace orders with 1 in the above query, as shown.
These 2 solutions are generally the recommended ways to calculate multiple counts using a single query.
3. Using Sub query
This method is mentioned here only for the sake of completeness. In this solution, we use separate sub queries to calculate separate counts. It is not as efficient as the above two methods, since it requires the database engine to scan the entire table each time for a sub query. If you have large tables, then this solution will be very slow. So use it only if you are unable to use the above mentioned solutions.
mysql> SELECT sum(orders) AS total,
(select sum(orders) from sales where product='A') AS A_count,
(select sum(orders) from sales where product='B') AS B_count
FROM sales;
+-------+---------+---------+
| total | A_count | B_count |
+-------+---------+---------+
| 270 | 120 | 100 |
+-------+---------+---------+
In the above query, we use sub queries to calculate sum of orders column where product=A and product=B.
Conclusion
In this article, we have learnt how to get multiple counts in single SQL query in MySQL. We have learnt why it is better to use a single query to get multiple counts, instead of using separate ones. We have learnt how to do this using CASE statement, using IF statement and using sub query. It is especially useful if you have a large table and you need to get multiple counts without running separate queries on them.
Both the solutions, using CASE statement and using IF statement require only single table scans, whereas the one using sub queries requires multiple table scans. Depending on your requirement, you can use any of these methods. Please note, you can use these solutions in other database systems too.
Also read:
Top 5 Online Courses to Learn MySQL
MySQL Select Top N Rows Per Group
How to Enable Remote Access in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.