SQL
Conditional Aggregation for Dynamic Reports in SQL
Generate dynamic summary reports and pivot-like results using SQL's conditional aggregation with CASE statements inside aggregate functions like SUM or COUNT.
SELECT
department_name,
SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) AS active_employees,
SUM(CASE WHEN status = 'Inactive' THEN 1 ELSE 0 END) AS inactive_employees,
COUNT(employee_id) AS total_employees
FROM
employees
GROUP BY
department_name
ORDER BY
department_name;
How it works: Conditional aggregation uses `CASE` expressions within aggregate functions (like `SUM`, `COUNT`, `AVG`) to create dynamic columns based on specific conditions. For example, `SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END)` counts only active employees within each `GROUP BY` department, effectively pivoting data without requiring a dedicated `PIVOT` keyword (which isn't available in all SQL dialects). This is highly useful for creating flexible summary reports.