SQL
Conditional Aggregation with CASE WHEN for Flexible Reporting
Learn how to use `CASE WHEN` inside aggregate functions like `SUM()` to perform conditional counts or sums, enabling flexible reporting and pivot-like structures in SQL.
SELECT
region,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
COUNT(order_id) AS total_orders
FROM orders
GROUP BY region
ORDER BY region;
How it works: This snippet uses `CASE WHEN` expressions within aggregate functions (`SUM` in this example) to perform conditional counting or summation. It allows you to create pivot-table-like reports where different conditions contribute to separate columns in the aggregated result, providing flexible and powerful reporting capabilities in a single query without needing multiple subqueries or post-processing.