Count Case When in SQL

If you have ever worked with relational databases or any SQL flavor, you are probably familiar with the COUNT function or the CASE operator. We use the COUNT function to aggregate and determine the number of records from a given result.

However, in some cases, we may need to combine the count function and the case statement to determine the number of records that match a specific condition.

In this tutorial, we will learn how to use the COUNT CASE WHEN statement to determine the number of occurrences of a specific condition within a column or a column set.

We can use this statement to get a summary or determine the aggregated data for a specific column.

SQL COUNT CASE WHEN

We can express the syntax of the COUNT CASE WHEN statement as shown in the following:

SELECT COUNT(CASE WHEN condition THEN 1 ELSE NULL END) AS alias FROM table_name;

Let us break down the previous syntax into smaller sections:

The first part is the count function which allows us to determine the number of values in the expression.
Using the CASE operator allows us to specify the expression which is a conditional statement that returns the matching values.
The THEN clause allows to specify the result to return if the condition evaluates to true.
We use the ELSE statement to define the result return if the condition is false.
The NULL clause specifies that we should return no value if the condition is false.
We also define an alias for the result set using the AS keyword.
We specify the table name from which we wish to retrieve the target data.

Practical Example

To better demonstrate how we can work with the SQL COUNT CASE WHEN statement, let us use a real-world database such as the Sakila database.

Problem:

Our goal is to determine the number of rentals that are made by each customer in the rental table of the Sakila database.

Solution:

To fetch the previous data, we can use the COUNT CASE WHEN clause as demonstrated in the following example query:

SELECT customer_id, COUNT(CASE WHEN return_date IS NOT NULL THEN 1 ELSE NULL END) AS total_rentals_per_customer
FROM rental
GROUP BY customer_id
ORDER BY total_rentals_per_customer DESC;

In the previous query, we use the select statement to select the customer_id column from the rental table and the count for the number of rentals that are made by each customer using the COUNT CASE WHEN statement.

The CASE WHEN clause allows us to evaluate whether the return_date column is null, indicating that it’s not an active rental.

We then group the result based on the customer_id column and order the resulting set based on the number of rentals per customer in ascending order.

This should return a table as shown in the following:

For better readability and data interpretation, we can use a simple SQL join to include the customer’s name as shown in the following:

SELECT c.customer_id, c.first_name, c.last_name, COUNT(CASE WHEN r.return_date IS NOT NULL THEN 1 ELSE NULL END) AS total_rentals_per_customer
FROM rental r
JOIN customer c ON r.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY total_rentals_per_customer DESC;

This should use the customer_id column to fetch the corresponding names from the customer table.

The resulting table is as follows:

There you have it!

Conclusion

We explored the fundamentals of working with the SQL COUNT CASE WHEN clause which allows us to generate and calculate the aggregated data for a specific column.