How to Have Multiple Counts in SQL Server

When working with large datasets in SQL Server, you may find the need to calculate multiple counts in a single query. This can help reduce the number of queries and improve performance. In this tutorial, we will show you how to execute multiple counts within a single SQL query and how to customize the counts based on different criteria.

Using Multiple COUNT() Functions

The COUNT() function in SQL Server counts the number of rows that match a specified condition. If you need to perform multiple counts based on different conditions, you can use multiple COUNT() functions in a single SELECT query.

SELECT 
    COUNT(*) AS TotalRows,
    COUNT(CASE WHEN Status = 'Active' THEN 1 END) AS ActiveRows,
    COUNT(CASE WHEN Status = 'Inactive' THEN 1 END) AS InactiveRows
FROM Users;
                                

In the query above, we use three COUNT() functions to get the total number of rows, the number of active users, and the number of inactive users. Each COUNT() function uses a CASE statement to apply the condition and count only rows that meet the specified criteria.

Using COUNT() with GROUP BY

If you want to get counts for different categories, you can use COUNT() with GROUP BY. For example, let’s say you want to count how many active and inactive users exist for each department:

SELECT 
    Department,
    COUNT(CASE WHEN Status = 'Active' THEN 1 END) AS ActiveUsers,
    COUNT(CASE WHEN Status = 'Inactive' THEN 1 END) AS InactiveUsers
FROM Users
GROUP BY Department;
                                

This query groups the results by the department and calculates the counts for active and inactive users within each department.

Combining Multiple Aggregate Functions

You can combine multiple aggregate functions in a single query, like COUNT(), SUM(), and AVG(). This is useful when you need to perform multiple calculations in one go. For example:

SELECT 
    Department,
    COUNT(*) AS TotalUsers,
    SUM(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) AS ActiveUsers,
    AVG(Salary) AS AverageSalary
FROM Users
GROUP BY Department;
                                

In this query, we calculate the total number of users, the number of active users, and the average salary for each department. The COUNT() function counts the total rows, and the SUM() function counts active users by summing up the 1s in the CASE statement.

Conclusion

SQL Server makes it easy to execute multiple counts within a single query. By using functions like COUNT() with CASE statements and GROUP BY, you can calculate multiple aggregates in a single step, improving both performance and readability of your SQL queries. Experiment with different conditions and see how you can optimize your queries to handle large datasets efficiently.