How to use Count() with Case Statement In SQL

Here, we will make use of Count() function and add a case condition as arguments, so that it will count for specific conditions and return us counts for different items in the table.

Syntax:

SELECT count(*) as <alias_one>,
COUNT(case when <condition_1> then 1 else null end) as <alias_2>,
COUNT(case when <condition_2> then 1 else null end) as <alias_3>,
. . .
FROM <Table>;

Example

Let’s counting the total number of orders and the number of orders placed on a specific date (2024-02-15) and the number of orders with a quantity of 12 from an “Orders” table in SQL Server.

Query:

SELECT count(*) as total_count,
COUNT(case when OrderDate='2024-02-15' then 1 else null end) as [OrderDate_2024-02-15_Count],
COUNT(case when Quantity=12 then 1 else null end) as Quantity_12_Count
FROM Orders;

Output:

Explanation: Here it returns 5 for first count, 2 for 2nd that is for the OrderDate 2024-02-15 and 2 for the records with same quantity 12.

In SQL Server, obtaining multiple counts with a single query is a common requirement, especially when we are analyzing data across different conditions. Whether we are tallying the number of active and inactive users or counting orders based on their status by using a single query can speed our data retrieval process and improve query performance. In the article, we will learn about how to Get Multiple Counts With a Single Query in an SQL Server with the help of various techniques and methods along with their examples and so on.