SQL
Perform Conditional Aggregation for Pivot-like Reports
Learn to create custom aggregate reports by conditionally counting or summing values based on specific criteria within a single query, mimicking pivot table functionality without complex syntax.
SELECT
product_category,
COUNT(CASE WHEN order_status = 'pending' THEN 1 ELSE NULL END) AS pending_orders,
COUNT(CASE WHEN order_status = 'completed' THEN 1 ELSE NULL END) AS completed_orders,
SUM(CASE WHEN order_status = 'completed' THEN order_total ELSE 0 END) AS total_completed_revenue,
COUNT(*) AS total_orders_in_category
FROM
orders
GROUP BY
product_category
ORDER BY
product_category;
How it works: This query uses conditional aggregation with `CASE` statements inside `COUNT()` and `SUM()` functions to generate a pivot-like report. For each `product_category`, it counts orders based on their `order_status` (pending or completed) and calculates the total revenue for completed orders. This technique allows you to transform row-level data into summary columns, providing quick insights into different aspects of orders within each category.