SQL
Generate Pivot-like Reports with Conditional Aggregation
Learn to create flexible summary reports by conditionally aggregating data using CASE WHEN expressions inside SQL aggregate functions, mimicking pivot tables.
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) AS completed_sales,
SUM(CASE WHEN status = 'pending' THEN total_amount ELSE 0 END) AS pending_sales,
COUNT(CASE WHEN status = 'cancelled' THEN 1 ELSE NULL END) AS cancelled_orders
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
How it works: This query uses conditional aggregation with CASE WHEN statements inside SUM and COUNT functions to create a pivot-like report. It calculates total sales for 'completed' and 'pending' orders, and the count of 'cancelled' orders, all grouped by month. This technique allows you to transform rows into columns based on specific conditions, providing a powerful way to generate custom summary reports directly within SQL for your web application dashboards or analytics.