SQL
Performing Conditional Aggregation with SQL CASE Statements
Learn to perform powerful conditional aggregations in SQL using `CASE` statements within aggregate functions (e.g., `SUM`, `COUNT`) to get multiple filtered counts or sums in 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 payment_method = 'credit_card' THEN order_total ELSE 0 END) AS credit_card_sales,
SUM(CASE WHEN payment_method = 'paypal' THEN order_total ELSE 0 END) AS paypal_sales
FROM
orders
GROUP BY
product_category
ORDER BY
product_category;
How it works: Conditional aggregation uses `CASE` expressions inside aggregate functions like `COUNT` or `SUM` to categorize and aggregate data within a single query. Instead of writing multiple queries or subqueries for different conditions, this technique allows you to create several summary columns based on distinct criteria in one pass, greatly improving query efficiency and readability for reports.