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.

How to Get Multiple Counts With Single Query?

The COUNT() function is used to return the number of rows that match a specified condition. It can be used in various scenarios to count the number of records in a table. Two methods can be used to get the Multiple Counts With a Single Query in SQL Server which are given below.

1. Using Count( ) with CASE Statement
2. Using Count( ) and Sum( ) with CASE Statement

let’s Setting Up an Environment to Get Multiple Counts

But before that, let us consider creating a sample Database with some random records. We will create an as-usual Orders table with 5 random records:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,a
    ProductName VARCHAR(50),
    Quantity INT,
    OrderDate DATE
);

-- 5 rows inserted with 2 products orderDate as same (i.e. Product B and Product C) and 2 rows with same quantity
INSERT INTO Orders (OrderID, ProductName, Quantity, OrderDate) VALUES (1, 'Product A', 10, '2024-02-14');
INSERT INTO Orders (OrderID, ProductName, Quantity, OrderDate) VALUES (2, 'Product B', 5, '2024-02-15');
INSERT INTO Orders (OrderID, ProductName, Quantity, OrderDate) VALUES (3, 'Product C', 8, '2024-02-15');
INSERT INTO Orders (OrderID, ProductName, Quantity, OrderDate) VALUES (4, 'Product D', 12, '2024-02-17');
INSERT INTO Orders (OrderID, ProductName, Quantity, OrderDate) VALUES (5, 'Product E', 7, '2024-02-18');

Output:

Output

Explanation: In the above Query, Our Orders table has been created.

1. Using Count() with Case Statement

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:

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.

2. Using Count() and Sum() with Case Statement

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:

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.

Conclusion

Overall, the SQL Server offers efficient ways to retrieve multiple counts with a single query, which can significantly improve performance and simplify code. By using the COUNT() and SUM() functions with CASE statements, developers can easily specify different conditions to count records based on various criteria. This eliminates the need for multiple queries or complex logic, making the process more streamlined and manageable.