SQL
Perform Conditional Aggregation with CASE and Aggregate Functions
Learn to create summary reports by counting or summing based on conditions within a single aggregate query, effectively pivoting data with `CASE` expressions.
SELECT
department_id,
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_employees_count,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_employees_count,
COUNT(*) AS total_employees_in_department
FROM
employees
GROUP BY
department_id
ORDER BY
department_id;
How it works: This query uses conditional aggregation with `CASE` statements inside `SUM` to count employees by their status within each department. For each `department_id`, it calculates the number of 'active' and 'inactive' employees, and the total number of employees. This technique is powerful for creating pivot-like summaries in a single SQL query, providing multiple aggregated values per group without complex joins or multiple subqueries.