SQL
Calculate Conditional Aggregations (Pivot-like)
Learn how to create pivot-like summaries in SQL by aggregating data based on specific conditions using CASE statements within aggregate functions. Highly useful for reporting.
SELECT
category,
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_sum,
SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_sum,
COUNT(CASE WHEN status = 'completed' THEN 1 ELSE NULL END) AS completed_count
FROM
orders
GROUP BY
category;
How it works: This snippet demonstrates how to perform conditional aggregations, effectively creating a pivot table without using a specific PIVOT clause. It uses CASE statements inside aggregate functions (SUM, COUNT) to conditionally sum or count values based on criteria like `status`. This is highly useful for generating summary reports where different metrics are required for different categories or states of data, providing flexible data analysis.