Conditional Aggregation

Uses CASE inside aggregate functions.

Source data — Pattern 11: Interview SQL Patterns

All queries run against the shared employees table (see schema.sql). Topics 71 and 72 introduce an orders table to solve consecutive records and gaps-and-islands problems. Topics 73 and 74 introduce a monthly_sales table to demonstrate PIVOT and UNPIVOT operations.

Conditional Aggregation — source table 1

Conditional Aggregation — source table 2

Conditional Aggregation — source table 3

Conditional Aggregation — source table 4

What it does

Uses CASE inside aggregate functions.

Problem

Count employees by salary category.

SELECT
COUNT(CASE WHEN salary<50000 THEN 1 END) AS low_salary,
COUNT(CASE WHEN salary BETWEEN 50000 AND 80000 THEN 1 END) AS medium_salary,
COUNT(CASE WHEN salary>80000 THEN 1 END) AS high_salary
FROM employees;

Result

Conditional Aggregation — result 1