SQL
Conditional Aggregation for Dynamic Summary Reports
Generate powerful summary reports with conditional aggregation using `CASE` statements inside aggregate functions. Count or sum specific categories within a single query.
-- Sample table for user orders
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INT,
order_date DATE,
status VARCHAR(50), -- e.g., 'pending', 'completed', 'cancelled'
amount DECIMAL(10, 2)
);
-- Insert sample data
INSERT INTO orders (user_id, order_date, status, amount) VALUES
(101, '2023-10-26', 'completed', 150.00),
(102, '2023-10-26', 'pending', 75.50),
(101, '2023-10-26', 'completed', 200.00),
(103, '2023-10-27', 'cancelled', 50.00),
(102, '2023-10-27', 'completed', 120.00),
(101, '2023-10-27', 'pending', 300.00),
(104, '2023-10-28', 'completed', 99.99),
(103, '2023-10-28', 'completed', 10.00);
-- Daily order summary: total orders, completed, pending, cancelled counts
SELECT
order_date,
COUNT(order_id) AS total_orders,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders,
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS total_completed_amount
FROM orders
GROUP BY order_date
ORDER BY order_date;
-- User-specific summary: how many orders of each status
SELECT
user_id,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders
FROM orders
GROUP BY user_id
ORDER BY user_id;
How it works: This snippet demonstrates conditional aggregation, a technique to perform different aggregations (like `COUNT` or `SUM`) based on specific conditions within a single `GROUP BY` query. By using `CASE` statements inside aggregate functions, you can effectively "pivot" data, creating columns for different statuses or categories in your summary reports without needing separate queries or complex joins. `COUNT(CASE WHEN condition THEN 1 END)` only counts non-null values, effectively counting rows where the condition is met.