Conditional Aggregation for Dynamic Reports

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
sql
SELECT 
  department_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_tier
FROM employees
GROUP BY department_id;

Breakdown

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.

GROUP BY department_id

Organizes the final metrics by the specified dimension.

Previous snippet Next snippet

From your library

Conditional Aggregation for Dynamic Reports

Related