Conditional Aggregates with CASE

Sometimes a filter is too blunt. "How many engineers are there?" is a simple WHERE question. But a manager asking "how many people earn over 600000, and how many under?" wants both numbers in a single row — a side-by-side comparison that a single WHERE clause cannot provide, because WHERE keeps rows permanently and throws the rest away. Running two separate queries and combining the answers in a spreadsheet is wasteful and fragile.

Conditional aggregates solve this by embedding a CASE expression inside an aggregate function. CASE evaluates a condition on every row and returns a value only when the condition is true. The aggregate then collects those returned values and ignores the NULLs produced by the failed cases. In one query you can count high earners, count low earners, and sum each group's budget, all as separate columns.

This lesson teaches CASE inside COUNT and SUM, the subtle difference between filtering and conditional counting, and how to build summary tables that read like dashboards.

flowchart TB
  A["All employees"] --> C1["CASE WHEN salary > 600000<br/>THEN 1 END"]
  A --> C2["CASE WHEN salary <= 600000<br/>THEN 1 END"]
  C1 -->|COUNT| N1["High earners"]
  C2 -->|COUNT| N2["Low earners"]
  style N1 fill:#0e7490,color:#fff
  style N2 fill:#0e7490,color:#fff

CASE splits the same column into two streams; each aggregate counts only the rows it cares about.

CASE inside an aggregate

A CASE expression has a simple shape: CASE WHEN condition THEN value END. It runs once per row, checks the condition, and returns the value if the condition is true. If the condition is false, it returns NULL.

When you wrap that inside COUNT, the function ignores NULLs, so only the rows that satisfied the condition are tallied. COUNT(CASE WHEN salary > 600000 THEN 1 END) counts high earners because every other row contributes NULL, which COUNT discards.

You can also use SUM(CASE WHEN ... THEN 1 ELSE 0 END). Here the ELSE 0 ensures every row produces a number — 1 for true, 0 for false — and SUM adds them all up. Both patterns arrive at the same count, but SUM makes the intent explicit: you are adding up ones, not merely counting whatever is left.

COUNT and SUM with CASE produce the same result through different paths.

SELECT
  COUNT(CASE WHEN salary > 600000 THEN 1 END) AS high_earners,
  SUM(CASE WHEN salary > 600000 THEN 1 ELSE 0 END) AS also_high_earners,
  COUNT(*) AS total
FROM employees;

The shape of a conditional count

The two most common patterns are COUNT(CASE WHEN ... THEN 1 END) and SUM(CASE WHEN ... THEN 1 ELSE 0 END). Both answer "how many rows meet this condition?" but they reach the answer through different paths. COUNT ignores NULLs, so rows that fail the condition simply vanish from the tally. SUM adds numbers, so failed rows must explicitly contribute 0 to avoid changing the total.

Choose COUNT when you want the logic to read as "count the rows that match." Choose SUM when you are building a scorecard with many conditions and want every row to contribute a visible number, even if that number is zero. Both are correct; the difference is readability and habit.

WHERE versus conditional aggregates

These two approaches look interchangeable but serve different questions. WHERE salary > 600000 removes every low earner from the query entirely. After that filter, COUNT(*) can only count high earners, and you cannot also ask for the low-earner total in the same query because those rows are gone forever.

A conditional aggregate keeps every row in play. It categorises each row as it passes through, letting one query produce several columns from the same underlying data. This is essential for comparisons: "active versus completed project budgets" or "Oslo headcount versus Lisbon headcount." Use WHERE when you genuinely do not care about the excluded rows; use CASE when you need both groups side by side in a single result.

flowchart LR
  A["All rows"] --> W["WHERE salary > 600000<br/>removes low earners"]
  W --> C["COUNT(*)<br/>only high earners"]
  A --> CASE["CASE WHEN ...<br/>categorises every row"]
  CASE --> H["COUNT high"]
  CASE --> L["COUNT low"]
  style W fill:#b45309,color:#fff
  style CASE fill:#0e7490,color:#fff

WHERE discards rows permanently; CASE keeps every row and routes it to the right aggregate.

Multiple conditional counts in one query — a dashboard row.

SELECT
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count,
  COUNT(CASE WHEN status = 'on hold' THEN 1 END) AS on_hold_count
FROM projects;

Conditional SUM

COUNT is not the only aggregate that benefits from CASE. SUM(CASE WHEN status = 'active' THEN budget ELSE 0 END) adds up budgets only for active projects, returning a single total while silently treating every other project's budget as zero. This pattern is how financial dashboards produce "active revenue" and "completed revenue" in one row without running separate queries and stitching them together afterwards.

The ELSE 0 is important here. SUM ignores NULL, so a missing ELSE would make non-matching rows contribute NULL instead of zero. The final total would still be arithmetically correct because NULL does not change a sum, but explicit ELSE 0 makes the intent obvious to the next reader and prevents confusion when someone later copies the pattern into an AVG, where NULLs do matter.

Conditional SUM builds side-by-side totals from the same rows.

SELECT
  SUM(CASE WHEN status = 'active' THEN budget ELSE 0 END) AS active_budget,
  SUM(CASE WHEN status = 'completed' THEN budget ELSE 0 END) AS completed_budget,
  SUM(budget) AS total_budget
FROM projects;

Building a dashboard row

The real power of conditional aggregates appears when you stack several side by side. A single query can produce a row that reads like a management report: five active projects worth 5.65 million, two completed projects worth 1.1 million, one on-hold project worth 0.15 million. Each column is an independent conditional aggregate, yet they all draw from the same underlying rows.

Because the database scans the table only once and computes every column in the same pass, this is also far more efficient than running three separate queries and stitching the results together in application code. One pass, one row, every answer.

Ordering the results

A query full of conditional aggregates returns exactly one row, so ORDER BY is rarely needed. But when you build a grouped query that also uses CASE — for example, grouping by department and counting high earners per department — adding ORDER BY makes the report readable. Sort by the computed column that matters most, and always alias complex expressions so the ORDER BY clause stays short and clear.

Exercise

Count how many employees earn more than 700000. Name the column high_earners.

SELECT
  -- conditional count here
FROM employees;

SELECT COUNT(CASE WHEN salary > 700000 THEN 1 END) AS high_earners FROM employees;

Exercise

Return one row with two columns: active_projects (count of active projects) and completed_projects (count of completed projects).

SELECT
  -- two conditional counts here
FROM projects;

SELECT COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_projects, COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_projects FROM projects;

Exercise

This query tries to count active projects but returns 8 — the total number of projects. Fix it.

SELECT COUNT(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_projects
FROM projects;

SELECT COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_projects FROM projects;

Exercise

Return one row showing the total budget for active projects as active_budget and the total budget for non-active projects as other_budget. Use conditional SUM.

SELECT
  -- two conditional sums here
FROM projects;

SELECT SUM(CASE WHEN status = 'active' THEN budget ELSE 0 END) AS active_budget, SUM(CASE WHEN status != 'active' THEN budget ELSE 0 END) AS other_budget FROM projects;

Exercise

In one row, show: total_employees, pre_2020 (hired before 2020-01-01), and from_2020 (hired on or after 2020-01-01).

SELECT
  -- three aggregates here
FROM employees;

SELECT COUNT(*) AS total_employees, COUNT(CASE WHEN hired_on < '2020-01-01' THEN 1 END) AS pre_2020, COUNT(CASE WHEN hired_on >= '2020-01-01' THEN 1 END) AS from_2020 FROM employees;

Recap

CASE WHEN condition THEN value END evaluates per row and returns NULL when the condition fails.
Wrapping CASE inside COUNT tallies only the matching rows because COUNT ignores NULLs.
Wrapping CASE inside SUM lets you total a column selectively; use ELSE 0 so non-matching rows contribute nothing.
COUNT(CASE ... ELSE 0 END) is a trap — the zero is counted as a real value. Remove ELSE 0 or switch to SUM.
WHERE removes rows permanently; CASE keeps every row and routes it to the right aggregate. Choose based on whether you need side-by-side comparisons.
Multiple conditional aggregates can share one SELECT list, building dashboard-style summaries in a single query.

Next you will learn to group rows by computed expressions such as date parts and salary bands, rather than grouping by raw columns alone.

Conditional Aggregates with CASE

CASE inside an aggregate

The shape of a conditional count

WHERE versus conditional aggregates

Conditional SUM

Building a dashboard row

Ordering the results

Recap

Checkpoint quiz

Go deeper — technical resources