Conditional Aggregation with CASE WHEN

When Basic Aggregates Aren’t Enough

So far, your aggregates operate on all rows (or all rows in a group). But what if you need to count only certain values? Sum only specific items? Get multiple conditional counts in one query?

You could run separate queries with different WHERE clauses. But there’s a better way: put CASE WHEN inside your aggregate function.

Quick CASE Refresher

Before we dive in, here’s the CASE syntax:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

CASE evaluates conditions in order and returns the result for the first true condition. If none match, it returns the ELSE value (or NULL if there’s no ELSE).

The Pattern: SUM(CASE WHEN…)

Here’s the most common pattern. It counts or sums values that meet a condition:

SELECT
  COUNT(*) AS total_employees,
  SUM(CASE WHEN department = 'HR' THEN 1 ELSE 0 END) AS hr_count,
  SUM(CASE WHEN department = 'Admin' THEN 1 ELSE 0 END) AS admin_count,
  SUM(CASE WHEN department = 'Account' THEN 1 ELSE 0 END) AS account_count
FROM techcorp_workforce;

One row back with four columns: total, HR count, Admin count, Account count. No GROUP BY needed.

How It Works

For each row, CASE WHEN checks the condition. If true, it returns 1. If false, it returns 0. SUM adds up all those 1s and 0s. The result: a count of matching rows.

Watch Out for NULLs

If a column value is NULL, comparisons like salary < 80000 are neither true nor false. The CASE falls through to the ELSE.

-- If salary is NULL, this returns 0 (the ELSE)
SUM(
CASE
  WHEN salary < 80000 THEN 1
  ELSE 0
END
)

This effectively treats unknown salaries as “not matching.” That’s usually fine, but be aware of it. If you need to explicitly handle NULLs, add a condition:

SUM(
CASE
  WHEN salary IS NULL THEN NULL -- Exclude from count entirely
  WHEN salary < 80000 THEN 1
  ELSE 0
END
)

SELECT
  ROUND(
    100.0 * SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END)
    / COUNT(*),
    2
  ) AS pct_high_earners
FROM techcorp_workforce;

Conditional Sums

You can sum values conditionally, not just count.

Now you get payroll broken down by department, all in one row.

Creating Pivot Tables

Here’s where this gets really useful. You can create pivot-style cross-tabulations:

SELECT
  survived,
  SUM(CASE WHEN pclass = 1 THEN 1 ELSE 0 END) AS first_class,
  SUM(CASE WHEN pclass = 2 THEN 1 ELSE 0 END) AS second_class,
  SUM(CASE WHEN pclass = 3 THEN 1 ELSE 0 END) AS third_class
FROM titanic
GROUP BY survived;

This creates a cross-tabulation: survival status on the rows, passenger class on the columns. One query, and you’ve got a pivot table.

Titanic Survivors and Non-Survivors

Use SUM(CASE WHEN pclass = X THEN 1 ELSE 0 END) for each class, grouped by survived status.

Multiple Conditions

CASE WHEN can handle complex logic.

Inspections by Risk Category

Use SUM(CASE WHEN risk_category = ... THEN 1 ELSE 0 END) for each category. Remember to handle the NULL risk category as a separate group.

Share of Active Users

Calculate a percentage using SUM(CASE WHEN) divided by COUNT(*). Use 100.0 * to avoid integer division.

Key Takeaways

• SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts matching rows
• This lets you get multiple conditional counts without GROUP BY
• NULL values fall through to ELSE (treated as non-matching)
• Use it for percentages: conditional count divided by total count
• Use 100.0 for percentages to avoid integer division
• You can sum values conditionally, not just count
• Combined with GROUP BY, you can create pivot-style reports

What’s Next

You’ve now got all the building blocks. The final lesson pulls everything together with complex queries that combine filtering, grouping, and conditional aggregation.