SQL
Generate Complex Reports with SQL Conditional Aggregation
Use CASE statements within aggregate functions like SUM() or COUNT() to create comprehensive summary reports with multiple conditional metrics in a single SQL query.
SELECT
order_date,
COUNT(CASE WHEN status = 'pending' THEN 1 ELSE NULL END) AS pending_orders,
COUNT(CASE WHEN status = 'completed' THEN 1 ELSE NULL END) AS completed_orders,
SUM(CASE WHEN payment_method = 'credit_card' THEN total_amount ELSE 0 END) AS credit_card_sales,
SUM(CASE WHEN payment_method = 'paypal' THEN total_amount ELSE 0 END) AS paypal_sales,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_sales
FROM
orders
GROUP BY
order_date
ORDER BY
order_date;
How it works: Conditional aggregation leverages the `CASE` statement inside aggregate functions (like `SUM()`, `COUNT()`, `AVG()`) to apply different aggregation criteria within the same query. This technique allows you to create highly detailed summary reports, generating multiple distinct metrics (e.g., counts of different statuses, sums for different categories) as separate columns in a single pass over your data, avoiding multiple subqueries or joins.