How to use Count() and Sum() with Case Statement In SQL
Here the syntax remains same as above method with replacement of count to sum from second variable
Syntax:
SELECT count(*) as <alias_one>,
SUM(case when <condition_1> then 1 else 0 end) as <alias_2>,
SUM(case when <condition_2> then 1 else 0 end) as <alias_3>,
. . .
FROM <Table>;
Example
Let’s calculates the total number of orders, 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:
SELECTcount(*) as total_count,
SUM(case when OrderDate='2024-02-15' then 1 else 0 end) as [OrderDate_2024-02-15_Count],
SUM(case when Quantity=12 then 1 else 0 end) as Quantity_12_Count
FROM Orders;
Output:
Explanation: This sums the count for OrderDate and Quantity with given condition and takes as 1 if it satisfies else 0. With this we can implement to get multiple counts in single row without having to create multiple SELECT statements, but need to use CASE conditional statements to add extra conditions you build the count for. You can also create counts per group if you use GROUP BY clause along with the above syntax.
How to Get Multiple Counts With Single Query in SQL Server
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.