sql / intermediate
Snippet
Pivoting Data via Conditional Aggregation with CASE
Combining aggregate functions with CASE expressions allows conditional counting and pivoting within a single table scan. This pattern avoids multiple separate joins or subqueries.
snippet.sql
sql
1
2
3
4
5
6
SELECTdepartment_id,SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_count,SUM(CASE WHEN status = 'ON_LEAVE' THEN 1 ELSE 0 END) AS leave_countFROM employeesGROUP BY department_id;
Breakdown
1
SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_count,
Conditionally converts status matches into 1s and non-matches into 0s before summing.
2
SUM(CASE WHEN status = 'ON_LEAVE' THEN 1 ELSE 0 END) AS leave_count
Aggregates employees on leave into a separate column within the same query pass.
3
GROUP BY department_id;
Groups output rows by department so each row represents one department.