sql / expert
Snippet
Conditional Aggregation for Dynamic Reports
Conditional aggregation uses CASE statements inside aggregate functions to pivot data or perform multi-category counts in a single pass over the table, significantly improving performance compared to multiple subqueries.
snippet.sql
1
2
3
4
5
6
7
SELECTdepartment_id,COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_tier,COUNT(CASE WHEN salary BETWEEN 50000 AND 100000 THEN 1 END) AS mid_tier,COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_tierFROM employeesGROUP BY department_id;
Breakdown
1
COUNT(CASE WHEN salary < 50000 THEN 1 END)
The CASE expression returns 1 for specific rows, which COUNT then tallies. NULLs (the default ELSE) are ignored.
2
GROUP BY department_id
Organizes the final metrics by the specified dimension.