Course contentsShow
← Data Science
Lesson 1033 of 2,14526. Advanced SQLFree lesson

CASE with Aggregation Functions

Apply CASE inside aggregate functions to conditionally count, sum, or average specific subsets of data.

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.