What’s New ?

The Top 10 favtutor Features You Might Have Overlooked

Read More

How to Use SQL COUNT() with CASE WHEN?

  • Mar 02, 2024
  • 8 Minutes Read
  • Why Trust Us
    We uphold a strict editorial policy that emphasizes factual accuracy, relevance, and impartiality. Our content is crafted by top technical writers with deep knowledge in the fields of computer science and data science, ensuring each piece is meticulously reviewed by a team of seasoned editors to guarantee compliance with the highest standards in educational content creation and publishing.
  • By Tanish Mallik
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. 


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

Table 1 Output Task Completion SQL COUNT CASE

SQL Query:

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



Table 1 Output Task Completion SQL COUNT CASE

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.


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.


COUNT(CASE WHEN condition1 THEN 1 END) AS aggregated_column1,
COUNT(CASE WHEN condition2 THEN 1 END) AS aggregated_column2,


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

SQL Query:

CREATE TABLE television_sales (
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:

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



Table 2 TV Sales Output SQL COUNT WHEN

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.


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

Table 3 Pilot Salary SQL COUNT CASE Advanced

SQL Query:

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



Table 3 Pilot Salary Output SQL COUNT CASE Advanced

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.


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. 

FavTutor - 24x7 Live Coding Help from Expert Tutors!

About The Author
Tanish Mallik
I am Tanish, a final-year computer science student with extensive expertise in SQL. My background in the field of data science has honed my strong analytical skills, and I also have hands-on experience in developing data integration and optimization pipelines using Python and MSBI. I am eager to contribute innovative ideas and make an impact in the ever-evolving world of technology.