In some scenarios, we might come across a situation in which we need to count only those records which satisfy certain conditions.
In cases like this, we can perform the conditional count using the SUM and CASE approach.
Example - In the Employees table, count the employees in a department whose salary is greater than 100K and those whose salary is between 50K to 100K.
The below SQL query will get us the desired output.
Select EmployeeID,
DeptID,
SUM (CASE When Salary > 100000 then 1 ELSE 0 END ) as Emp_100K ,
SUM (CASE When Salary >= 50000 and <= 100000 then 1 ELSE 0 END ) as Emp_50K
From Employees
Group by DeptID ;
Understanding the Query -
The CASE statements set the counters to 1 whenever the necessary condition is satisfied.
The SUM statement calculates the final count by adding up the 1s set by the respective CASE statements.