Conditional Aggregation

What if you want several counts side by side, like all employees and high earners in each department? Put a CASE inside the aggregate.

SUM with CASE counts the rows that match a condition:

SELECT department_id,
       COUNT(*) AS total,
       SUM(CASE WHEN salary >= 120000 THEN 1 ELSE 0 END) AS high_earners
FROM employees
GROUP BY department_id;

Each row adds 1 if it matches and 0 if it doesn't, so the SUM is the number of matches.

COUNT(CASE WHEN salary >= 120000 THEN 1 END) gives the same answer, because COUNT skips the NULLs that CASE returns for non-matches.

It works with any aggregate. Here's the payroll for people hired before 2019:

SELECT department_id,
       SUM(CASE WHEN hire_date < '2019-01-01' THEN salary ELSE 0 END) AS pre_2019_payroll
FROM employees
GROUP BY department_id;

Turning rows into columns like this is also called a pivot. It's one of the most common patterns in analytics interviews.