Obtaining Multiple Counts Using a Single SQL Query

1. Introduction

In SQL, we often need to retrieve multiple counts from a single table, such as counting records based on different conditions in one query. Instead of writing multiple queries, we can efficiently use a single query.

In this tutorial, we’ll learn how to count multiple labels in a column and explore various ways to retrieve multiple counts efficiently. This tutorial uses the table from Baeldung’s simple University database schema.

2. Obtaining Multiple Counts

Counts are aggregation functions used to return a single value. There are different approaches to retrieving multiple counts. Let’s see some of the common ways:

• using SUM with CASE
• using COUNT with IF
• using a subquery

Each one of the above has its effect on query performance. Add DESCRIBE before each query to understand how each query performs.

Let’s get the first ten rows of the Exam table:

 SELECT * FROM Exam
 LIMIT 10;
+----+----------+------------+-------+-----------+------------+
| id | semester | exam_date  | grade | course_id | student_id |
+----+----------+------------+-------+-----------+------------+
|  1 | SPRING   | 2022-07-11 | A+    | CS111     |       1001 |
|  2 | SPRING   | 2022-07-11 | A     | CS121     |       1001 |
|  3 | SPRING   | 2022-07-11 | A+    | CS122     |       1001 |
|  4 | FALL     | 2022-12-05 | B+    | CS211     |       1001 |
|  5 | FALL     | 2022-12-06 | B     | CS212     |       1001 |
|  6 | FALL     | 2022-12-07 | B+    | MA111     |       1001 |
|  7 | SPRING   | 2023-07-11 | A+    | CS411     |       1001 |
|  8 | SPRING   | 2023-07-11 | A+    | CS511     |       1001 |
|  9 | SPRING   | 2023-07-11 | F     | MA211     |       1001 |
| 10 | FALL     | 2023-12-05 | NULL  | CS121     |       1001 |
+----+----------+------------+-------+-----------+------------+
10 rows in set (0.016 sec)

 3. Using CASE in SUM()

The CASE statement allows us to implement conditional logic within a query. This makes it the most efficient way to achieve multiple counts because it enables us to process the entire dataset in a single scan.

We can use CASE in the SUM() query to count records that meet specific criteria.

Let’s suppose we aim to count the number of ‘SPRING’ and ‘FALL’ labels in the semester column of the Exam table:

 SELECT COUNT(*) as total,
       SUM(CASE WHEN semester = 'FALL' THEN 1 ELSE 0 END) AS fall_count,
       SUM(CASE WHEN semester = 'SPRING' THEN 1 ELSE 0 END) AS spring_count
 FROM Exam;
+-------+-----------+-------------+
| total |fall_count |spring_count |
+-------+-----------+-------------+
|   143 |        72 |          71 |
+-------+-----------+-------------+
1 row in set (0.001 sec)

In the above output, the CASE condition replaces the value that meets the criteria with ‘1’ and sums up the value to retrieve the total count.

4. Using IF in COUNT()

The IF logic is a bit similar to CASE in SUM, but it is more concise. It evaluates a condition and returns one value if logic is true and another if false. Similar to CASE in SUM, IF allows a single table scan to tally different groups.

To count the number of ‘FALL’ and ‘SPRING’ for the semester column in the Exam table using IF in COUNT(), let’s try this:

SELECT COUNT(*) AS total,
     COUNT(IF(semester='FALL', 1, NULL)) AS fall_count,
     COUNT(IF(semester='SPRING',1,NULL)) AS spring_count
FROM Exam;
+-------+-----------+-------------+
| total |fall_count |spring_count |
+-------+-----------+-------------+
|   143 |        72 |          71 |
+-------+-----------+-------------+
1 row in set (0.001 sec)

The output shows the count of each label in the semester column. In addition, the IF logic returns ‘1’ for each record that meets the criteria, and the COUNT() function counts the total records.

5. Using a Subquery

Using a subquery can slow down query execution time while also consuming more resources, especially when retrieving multiple records. For example, each subquery may trigger a table scan, leading to performance bottlenecks. Therefore, it’s essential to use subqueries only when necessary and to ensure they are optimized.

Let’s see the equivalent subquery:

SELECT COUNT(*) AS total_count,
    (SELECT COUNT(*) FROM Exam WHERE semester = 'FALL') AS fall_count,
    (SELECT COUNT(*) FROM Exam WHERE semester = 'SPRING') AS spring_count
 FROM Exam;
+------------+-------------+-------------+
| TotalCount | fall_count  | spring_count|
+------------+-------------+-------------+
|        143 |          72 |          71 |
+------------+-------------+-------------+
1 row in set (0.001 sec)

As seen above, each subquery counts specific subsets of the records based on different conditions in the WHERE clause. When working with larger tables, it’s important to understand when to avoid using multiple subqueries. This is due to how much CPU it may require to execute queries that return voluminous results.

6. Conclusion

In this article, we explored different ways to retrieve multiple counts in a single query. Both CASE in SUM and IF in COUNT require single table scans, making them more efficient, while subqueries should be used sparingly due to potential performance cost.