SQL
Perform Conditional Aggregation with CASE WHEN in SQL
Learn how to use CASE WHEN expressions within aggregate functions (SUM, COUNT) to perform conditional counting or summing in a single SQL query, generating flexible reports.
SELECT
status,
COUNT(CASE WHEN priority = 'High' THEN 1 ELSE NULL END) AS HighPriorityTasks,
COUNT(CASE WHEN priority = 'Medium' THEN 1 ELSE NULL END) AS MediumPriorityTasks,
COUNT(CASE WHEN priority = 'Low' THEN 1 ELSE NULL END) AS LowPriorityTasks,
COUNT(*) AS TotalTasks
FROM
tasks
GROUP BY
status;
How it works: This SQL query demonstrates conditional aggregation using `CASE WHEN` inside `COUNT()`. It allows you to count specific subsets of data based on certain conditions (e.g., priority levels) within each group (e.g., task status), all within a single `GROUP BY` clause. This is highly efficient for generating reports that categorize data across multiple dimensions without needing multiple subqueries or joins.