SQL
Perform Conditional Aggregation with CASE
Master SQL conditional aggregation using the CASE statement within aggregate functions (SUM, COUNT) to generate insightful reports from a single, efficient query.
SELECT
product_category,
COUNT(CASE WHEN order_status = 'completed' THEN 1 ELSE NULL END) AS completed_orders,
COUNT(CASE WHEN order_status = 'pending' THEN 1 ELSE NULL END) AS pending_orders,
SUM(CASE WHEN order_status = 'completed' THEN order_total ELSE 0 END) AS total_completed_revenue
FROM orders
GROUP BY product_category;
How it works: This snippet demonstrates conditional aggregation using the `CASE` statement inside aggregate functions. It allows you to count or sum values based on specific conditions within the same `GROUP BY` clause. For instance, it counts 'completed' and 'pending' orders and sums the `order_total` for completed orders, all grouped by `product_category` in a single pass.