How to Count Based on Condition in SQL Server?

In SQL Server, the COUNT() function is also utilized for tallying the number of records within a table. This article intends to explore the query, focusing on incorporating conditions into the COUNT() function in SQL Server. The COUNT() function in SQL Server is commonly utilized to count all records within a dataset.

Nonetheless, there are scenarios where counting records based on specific conditions becomes essential. Thankfully, SQL Server allows for the integration of conditions within the COUNT() function, enabling a more nuanced approach to record counting.

How to Count Based on Condition in SQL Server?

The COUNT() aggregate function holds significant importance in SQL Server queries, facilitating the counting of rows within a table. This function serves as a powerful instrument for data manipulation and aggregation.

While COUNT(*) fetches the overall row count by default, its flexibility becomes evident when conditions are incorporated through the DISTINCT or CASE expressions, allowing for more targeted counting based on specific criteria.

• Method 1: Using DISTINCT with Count() function
• Method 2: Using CASE with Count() function

Using DISTINCT with Count() function

To use the COUNT() function with the DISTINCT keyword in SQL Server, you can follow this syntax:

Syntax:

SELECT COUNT(DISTINCT column_name1)

FROM table_name

GROUP BY column_name2;

Using DISTINCT in COUNT() will count the unique values in the specified column, and using the GROUP BY clause applied to another table will count the values according to that table.

Example: Counting Rows Using COUNT() Function with DISTINCT

Let’s Consider a table of Employees where you have to count how many people belong to each department. To count that you have to use DISTINCT in the COUNT() function with GROUP BY clause, this will count the number of people working in each department.

SELECT
    Department,
    COUNT(DISTINCT EmployeeID) AS Employee_Count 
FROM
    Employees
GROUP BY
    Department;

Output:

Count using Distinct

Explanation: In the above output you can see that there is only 1 person who belongs to the ‘Customer Service’, ‘HR’, ‘Marketing’, ‘Operation’ and ‘Sales’ departments. 2 person working in ‘Finance’ department and 3 person working in ‘IT’ department.

Method 2: Counting using CASE with Count() function

Using the CASE keyword in COUNT() function will give a count based on the given condition and if that condition is true it will return the output specifically based on those conditions.

Syntax:

SELECT COUNT(CASE WHEN condition1 THEN expression1 ELSE null END) AS Alias1,

COUNT(CASE WHEN condition2 THEN expression2 ELSE null END) AS Alias2,

…FROM table_name

WHERE condition;

In the CASE expression – WHEN, if the condition evaluates to true, the corresponding THEN result is returned. If no conditions match, the ELSE result is returned.

Example: Counting Rows Using COUNT() Function with CASE

Suppose you have a ‘Employee‘ table and you have to count the number of people in each department. Using the COUNT() function with CASE, you can tally count based on a category field like ‘Department‘, ‘Position‘ etc.

Example:

SELECT
    Department,
    COUNT(CASE WHEN Department = 'IT' THEN 1 END) AS IT_Count,
    COUNT(CASE WHEN Department = 'HR' THEN 1 END) AS HR_Count,
    COUNT(CASE WHEN Department = 'Marketing' THEN 1 END) AS Marketing_Count,
    COUNT(CASE WHEN Department = 'Finance' THEN 1 END) AS Finance_Count,
    COUNT(CASE WHEN Department = 'Sales' THEN 1 END) AS Sales_Count,
    COUNT(CASE WHEN Department = 'Customer Service' THEN 1 END) AS CustomerService_Count,
    COUNT(CASE WHEN Department = 'Operations' THEN 1 END) AS Operations_Count
FROM
    Employees
GROUP BY
    Department;

Output:

Count using the Case keyword

Explanation: In the above example you can see that, except for the people working in that department the value in the other columns remains 0. So, you will get the count of how many people working in each department.

Conclusion

In conclusion, leveraging the COUNT() function with the DISTINCT keyword and the CASE statement in SQL Server provides a robust approach for data analysis. The DISTINCT keyword ensures that unique values are counted, while the CASE statement enables conditional logic to customize count calculations. By utilizing these functionalities together, SQL Server queries are capable of extracting precise information and supporting informed decision-making, thereby enhancing analytical accuracy and efficiency.