CASE with Aggregation Functions
You'll learn how to slice and dice your aggregations by embedding CASE logic directly inside SUM, COUNT, and AVG functions.
The Core Idea
Instead of filtering your entire dataset before aggregating, you can use CASE expressions inside aggregate functions to conditionally include or exclude specific rows from each calculation. Think of it as having multiple WHERE clauses working simultaneously within a single query—each aggregate function gets its own conditional logic.
How It Works
When you nest CASE inside an aggregate function, you're essentially telling the database: "Only count/sum/average rows that meet this specific condition." The CASE returns a value (or NULL) for each row, and the aggregate function processes those results.
Common patterns:
-- Conditional counting
COUNT(CASE WHEN condition THEN 1 END)
-- Conditional summing
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END)
-- Conditional averaging
AVG(CASE WHEN category = 'premium' THEN price END)
Why This Matters
This technique lets you calculate multiple segment-specific metrics in one pass through your data:
SELECT
department,
COUNT(*) AS total_employees,
COUNT(CASE WHEN salary > 75000 THEN 1 END) AS high_earners,
SUM(CASE WHEN status = 'active' THEN salary ELSE 0 END) AS active_payroll,
AVG(CASE WHEN years_experience > 5 THEN salary END) AS avg_senior_salary
FROM employees
GROUP BY department;
Each aggregate here answers a different conditional question—all without needing separate subqueries or multiple table scans.
Key Takeaway: Embedding CASE inside aggregate functions enables powerful conditional aggregation, letting you compute multiple filtered metrics efficiently in a single query by selectively including rows per calculation.