How to Get Multiple Counts in Single MySQL Query

Last updated on June 25th, 2024 at 04:11 am

MySQL is a popular database used by many organizations to store lots of data. Often database programmers and web developers need to be able to calculate count or sum of multiple columns, each using specific conditions. While it is easy to aggregate columns based on one or more columns, all using the same set of conditions, it can be tricky to do so using different conditions for each column. Nevertheless, you can still get multiple counts with different conditions or criteria in a single query in MySQL, using IF or CASE statements. You will need to use it along with the count() or sum() aggregation function, as per your need. In this article, we will look at how to get multiple counts with multiple conditions in MySQL. Before we proceed, it is important to understand how aggregation functions works, and also a learn a little bit about IF/CASE statements.

Table of Contents

Count() function in MySQL

Count() function is an aggregation function that counts number of rows in a table based on none or one or more conditions. Here is its syntax.

SELECT COUNT(column_name1), COUNT(column_name2), ...
FROM <TABLE_NAME> 
WHERE <CONDITION>;

In the above statement, you can see that counts of column1, column2 and so on are all calculated using the same set of conditions.

But how to get counts for where each column has different conditions? That is where IF/CASE statements are required.

If Statement in MySQL

If() function simply returns one value if a condition is true, and another value if it is false. Here is its syntax.

IF(condition, value_if_true, value_if_false)

Here is a simple example that returns 1 if product column is ‘A’, else it returns null.

if(product='A',1,null)

Now instead of counting the product column, we can use count() function on the result of this if() function to get a count of values where product=’A’.

count(if(product='A',1,null)) as A_count

Similarly, we can define another count where product=’B” and amount>200.

count(if(product='B' and amount>200,1,null)) as B_count

Now, we can put both the above counts in the same SQL query to calculate two different counts using completely different conditions for each column.

select count(if(product='A',1,null)) as A_count,
        count(if(product='B' and amount>200,1,null)) as B_count
        from product_sales;

Case Statement in MySQL

Similarly, you can also use CASE() statement for this purpose. It evaluates several different conditions and returns different values for each condition. It is more versatile than IF statement which allows you to return only 2 values. CASE statement allows you to work with more conditions and results. Here is its syntax.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Here is an example to use CASE statement to get conditionally count product column where product=’A’.

count(case when product='A' then 1 else null end) as A_count

Similarly, here is an example to count product column where product=’B’ and amount>200.

count(case when product='B' and amount>200 then 1 else null end) as B_count

Putting the two case statements together in one query, we obtain the following.

select 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;

How to Get Multiple Counts 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 |
+------+---------+------------+--------+

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 |
+-------------+---------+---------+

Conclusion

In this article, we have learnt a couple of different ways to aggregate table columns such that each column has different set of conditions. Instead of calling count() function directly on the column names, you need to call it on the result of IF() or CASE() statements. This allows you to mention completely different conditions in each IF/CASE statement, without using the WHERE clause at all. We have used count() function to do aggregations but you can also use other functions such as SUM(), MIN(), MAX(), etc. as per your requirement.

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.