SQL
Performing Conditional Aggregation for Cross-Tab Reports
Learn to create cross-tabulation reports using conditional aggregation with CASE statements inside aggregate functions to pivot data in SQL.
SELECT
category_name,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_items,
SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved_items,
SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) AS rejected_items,
COUNT(item_id) AS total_items
FROM
items
GROUP BY
category_name
ORDER BY
category_name;
How it works: This query demonstrates conditional aggregation, a powerful technique to pivot data without relying on database-specific PIVOT clauses. By using CASE statements inside aggregate functions like SUM or COUNT, you can count occurrences or sum values based on specific conditions, effectively turning row values into distinct columns for summary reports. This is highly useful for generating flexible cross-tabulations or dashboards showing counts by various statuses or types.