SQL
Aggregate Multiple Metrics with Conditional Counts
Learn how to use conditional aggregation with CASE WHEN inside aggregate functions to create a single report showing counts of different statuses or types in one SQL query.
SELECT
status,
COUNT(CASE WHEN priority = 'High' THEN 1 ELSE NULL END) AS high_priority_tasks,
COUNT(CASE WHEN priority = 'Medium' THEN 1 ELSE NULL END) AS medium_priority_tasks,
COUNT(CASE WHEN priority = 'Low' THEN 1 ELSE NULL END) AS low_priority_tasks,
COUNT(*) AS total_tasks
FROM
tasks
GROUP BY
status;
-- Alternatively, to pivot statuses into columns for a global summary:
SELECT
COUNT(CASE WHEN status = 'Pending' THEN 1 ELSE NULL END) AS pending_count,
COUNT(CASE WHEN status = 'Completed' THEN 1 ELSE NULL END) AS completed_count,
COUNT(CASE WHEN status = 'Cancelled' THEN 1 ELSE NULL END) AS cancelled_count
FROM
tasks;
How it works: This snippet demonstrates conditional aggregation, a technique where you use `CASE WHEN` expressions inside aggregate functions like `COUNT` or `SUM`. The first query groups tasks by their primary status and then counts how many tasks within each status have a 'High', 'Medium', or 'Low' priority. The second query shows how to 'pivot' status counts into columns, useful for summarizing data without grouping by the pivoted column itself. This allows generating multiple related metrics in a single, efficient query for reports or dashboards.