How to Use SQL COUNT() with CASE WHEN?

The COUNT() function in SQL is a very commonly used function, it is used with the SELECT function to count the data row values in a table. The output of the COUNT() function depends on the condition in the query. In this article, we will discuss COUNT CASE, COUNT CASE WHEN, and Advanced COUNT CASE.

The SELECT statement selects the number of rows for example in a student table, the SELECT statement will select the students by their names or IDs, and the COUNT() function will count the number of students selected. 

What is a COUNT CASE in SQL?

SQL’s COUNT CASE is used for counting the number of rows in a table with a condition. COUNT function when clubbed with CASE gives an aggregated data output. When the SQL query is broken into subqueries and blocks it becomes easier to deduce the actual interpretation of the query.

First, the inner case or condition is executed and then the compiler moves towards the outer query or the function that is the COUNT function. 

Syntax:

SELECT COUNT(CASE WHEN column_name = “value” THEN 1 END) AS count_example
FROM table_name;

Let us look at an example to understand the use of COUNT CASE:

CREATE TABLE task_completion (
task_id INT PRIMARY KEY,
task_name VARCHAR(50),
completed BOOLEAN
);

INSERT INTO task_completion (task_id, task_name, completed) VALUES
(1, 'Task A', true),
(2, 'Task B', false),
(3, 'Task C', true),
(4, 'Task D', true),
(5, 'Task E', false);

SELECT * FROM task_completion;

Table: Task_Completion

SQL Query:

SELECT
COUNT(CASE WHEN completed = true THEN 1 END) AS count_completed_tasks,
COUNT(CASE WHEN completed = false THEN 1 END) AS count_incomplete_tasks
FROM
task_completion;

Output:

In this example, we used the SELECT statement and the COUNT function with CASE. The first CASE statement checks if the task is completed, and the second one checks if the task is incomplete. The COUNT function counts the number of tasks based on the given conditions.

COUNT CASE WHEN in SQL

In SQL THE COUNT function with CASE WHEN is used to count the rows based on the specific conditions. This statement is used inside the COUNT function to define the conditions.

Syntax:

SELECT
COUNT(CASE WHEN condition1 THEN 1 END) AS aggregated_column1,
COUNT(CASE WHEN condition2 THEN 1 END) AS aggregated_column2,
FROM
table_name
WHERE
condition;

Let us look at an example to get a clear understanding of the COUNT CASE WHEN function.

SQL Query:

CREATE TABLE television_sales (
sale_id INT PRIMARY KEY,
television_type VARCHAR(50),
quantity_sold INT
);

INSERT INTO television_sales (sale_id, television_type, quantity_sold) VALUES
(1, 'LED', 100),
(2, 'Plasma', 50),
(3, 'LCD', 75),
(4, 'OLED', 120),
(5, 'LED', 80);

SELECT * FROM television_sales;

Table: Television_Sales

SQL Query:

SELECT
COUNT(CASE WHEN television_type = 'LED' THEN 1 END) AS count_led_sales,
COUNT(CASE WHEN television_type = 'Plasma' THEN 1 END) AS count_plasma_sales,
COUNT(CASE WHEN television_type = 'LCD' THEN 1 END) AS count_lcd_sales,
COUNT(CASE WHEN television_type = 'OLED' THEN 1 END) AS count_oled_sales
FROM
television_sales;

Output:

In this example, we first SELECT the values and then using the  COUNT function and CASE statement check for a specific television type ('LED', 'Plasma', 'LCD', 'OLED'), and the given COUNT function counts the number of sales for each type.

Advanced COUNT CASE

The advanced COUNT CASE in SQL is a statement used to count the rows that verify or match the criteria, these are used to categorize and count data based on different conditions.

SQL Query:

CREATE TABLE pilot_salary (
pilot_id INT PRIMARY KEY,
pilot_name VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO pilot_salary (pilot_id, pilot_name, salary) VALUES
(1, 'John Smith', 90000),
(2, 'Jane Doe', 75000),
(3, 'Bob Johnson', 110000),
(4, 'Alice Brown', 80000),
(5, 'David White', 95000);

SELECT * FROM pilot_salary;

Table: Pilot_Salary

SQL Query:

SELECT
COUNT(CASE WHEN salary >= 100000 THEN 1 END) AS count_high_salary,
COUNT(CASE WHEN salary BETWEEN 80000 AND 99999 THEN 1 END) AS count_medium_salary,
COUNT(CASE WHEN salary < 80000 THEN 1 END) AS count_low_salary
FROM
pilot_salary;

Output:

In this example, we are using the query for three COUNT functions and CASE statements. Each CASE statement checks for a specific salary range, and the associated COUNT function counts the number of pilots falling into each range. The three ranges are count_high_salary, count_medium_salary and count_low_salary.

Frequently Asked Questions (FAQs)

Can you count cases when in SQL?

Yes, we can COUNT and use the CASE function in SQL to count rows based on the given conditions. This is commonly used when there is a specific criterion for the query to meet. For example, counting the number of students from the students' database but with an average score above 80.00.

How to find count with condition in SQL?

We can use the CASE or CASE WHEN statement with COUNT in SQL to count the rows in a table with conditions or criteria.

Is SQL count case-sensitive?

No, the SQL count is not case-sensitive as it treats uppercase and lowercase characters as the same. Sometimes the case sensitivity depends on the type of database we are using for the system.

Conclusion

To sum up, in this article we gained knowledge about the COUNT CASE WHEN in SQL and how we can use it with several other conditions to produce a segmented output. Through the examples, we were able to clearly understand how and when to use which statement. Furthermore, we dealt with complex and advanced SQL queries using the COUNT function.