SQL CASE WHEN — Conditional Aggregation

CASE WHEN as a column expression

The simplest form is using CASE in the SELECT list to derive a new column. PostgreSQL evaluates each WHEN clause in order and returns the result of the first one that matches. If no WHEN matches and no ELSE is provided, the result is NULL.

There are two flavors: searched CASE (each WHEN is a full boolean expression) and simple CASE (CASE x WHEN value THEN ...). Searched CASE is more flexible and what you will use 90% of the time. Always include an ELSE — even if the answer is NULL — to make your intent explicit.

A common bug: forgetting that CASE returns a value, not a row. You cannot put a SELECT inside one branch and an INSERT inside another. Each branch must produce a value of compatible type — PostgreSQL will infer the result type from all branches and complain if they cannot be reconciled.

-- Bucket students by cumulative GPA. Names live on ods_person;
-- the GPA lives on ods_stu_acad_levels (stpr_gpa).
SELECT
  p.first_name,
  p.last_name,
  l.stpr_gpa,
  CASE
    WHEN l.stpr_gpa >= 3.7 THEN 'high_distinction'
    WHEN l.stpr_gpa >= 3.3 THEN 'distinction'
    WHEN l.stpr_gpa >= 2.0 THEN 'pass'
    ELSE 'at_risk'
  END AS academic_standing
FROM ods_stu_acad_levels l
JOIN ods_person p ON p.id = l.students_id
WHERE l.stpr_gpa IS NOT NULL
ORDER BY l.stpr_gpa DESC;

COUNT with CASE — conditional counts in one query

Putting CASE inside COUNT lets you count rows that match a condition, sliced beside an unrestricted COUNT in the same query. The trick: COUNT counts non-NULL values, so write CASE to return a value (any value) when the condition is true and NULL when it is false. Do not return 0 in the false branch — COUNT would count the zero.

This is the foundation of pivot-style reports. Need one row showing every student-term on record plus a column each for registered, on leave, and withdrew? Three CASE expressions inside COUNT, one query, one pass over the data. ods_student_terms holds one row per student per term, and sttr_current_status carries the code (R, L, W) with status_desc beside it.

A subtle and common mistake: writing CASE WHEN sttr_current_status = 'W' THEN 1 ELSE 0 END and wrapping it in COUNT. That counts every row — all 923 of them — because 0 is non-NULL. Either return NULL in the ELSE branch and use COUNT, or use SUM (covered next) to add up the 1s and 0s.

-- One row, four columns: every student-term plus a count per status
-- (sttr_current_status: R = Registered, L = Leave, W = Withdrew)
SELECT
  COUNT(*)                                              AS student_terms,
  COUNT(CASE WHEN sttr_current_status = 'R' THEN 1 END) AS registered,
  COUNT(CASE WHEN sttr_current_status = 'L' THEN 1 END) AS on_leave,
  COUNT(CASE WHEN sttr_current_status = 'W' THEN 1 END) AS withdrew
FROM ods_student_terms;

-- student_terms  registered  on_leave  withdrew
-- 923            760         86        77

SUM with CASE — weighted sums and conditional totals

SUM(CASE WHEN ... THEN 1 ELSE 0 END) gives you the same result as COUNT(CASE ... END) but is more portable across databases — every SQL dialect supports it. SUM with CASE also generalizes: instead of 1, you can return any value, including a column reference, to compute a weighted or filtered sum.

The pattern shines when you need conditional totals of a numeric column. Grant dollars that went to Pell-eligible students, loan totals for one aid year, credit hours completed by part-time students — all one SUM with a CASE inside. ods_financial_aid holds one row per student per aid year, so grouping by fa_year gives one row per year with no fan-out.

Combine multiple SUM(CASE ...) expressions with a GROUP BY to produce dashboard-shaped output: one row per category, multiple summary metrics across columns. This is genuinely the workhorse pattern for analyst-shaped SQL. (fa_year holds values like 2024-2025, which sort correctly as text. Term codes do not — see the CTE guide for why.)

-- Aid per award year: total, grants split by Pell eligibility, loans,
-- and the average grant among Pell-eligible students
SELECT
  fa_year,
  SUM(fa_total_awards)                                                  AS total_aid,
  SUM(CASE WHEN fa_pell_eligible = 'Y' THEN fa_total_grants ELSE 0 END) AS pell_eligible_grants,
  SUM(CASE WHEN fa_pell_eligible = 'N' THEN fa_total_grants ELSE 0 END) AS other_grants,
  SUM(fa_total_loans)                                                   AS loans,
  ROUND(AVG(CASE WHEN fa_pell_eligible = 'Y' THEN fa_total_grants END), 2)
                                                                        AS avg_pell_eligible_grant
FROM ods_financial_aid
GROUP BY fa_year
ORDER BY fa_year;

The FILTER clause — a cleaner alternative in PostgreSQL

PostgreSQL supports a FILTER (WHERE ...) clause attached directly to any aggregate. It does the same thing as wrapping the aggregate input in CASE — restrict which rows get aggregated — but with much cleaner syntax. COUNT(*) FILTER (WHERE sttr_current_status = 'W') reads as plainly as it sounds.

FILTER is the PostgreSQL-native way to write conditional aggregation. If you are writing PostgreSQL-only SQL, prefer it. CASE WHEN inside aggregates is more portable across databases (MySQL, SQL Server, Oracle), so legacy queries often still use it.

Behind the scenes the planner treats them similarly, so performance is not the deciding factor. The decision is usually about audience: if your team is mostly PostgreSQL, use FILTER for clarity. If queries are run on multiple databases, CASE WHEN keeps them portable.

-- Same query as the COUNT(CASE ...) example, with FILTER
SELECT
  COUNT(*)                                          AS student_terms,
  COUNT(*) FILTER (WHERE sttr_current_status = 'R') AS registered,
  COUNT(*) FILTER (WHERE sttr_current_status = 'L') AS on_leave,
  COUNT(*) FILTER (WHERE sttr_current_status = 'W') AS withdrew
FROM ods_student_terms;

-- 923 | 760 | 86 | 77  -- identical output

Common pitfalls

The 1-vs-0-vs-NULL distinction is the bug most people hit first. COUNT counts non-NULLs, so COUNT(CASE WHEN x THEN 1 ELSE 0 END) over-counts. Use ... THEN 1 END (no ELSE) with COUNT, or use SUM. Pick one pattern and use it consistently — mixing styles in one query is a debugging nightmare.

Type coercion bites in the ELSE clause. CASE WHEN ... THEN 1 ELSE 'none' END errors because PostgreSQL cannot reconcile integer and text. Make all branches return the same type, casting if needed.

Watch out for averages. AVG(CASE WHEN cond THEN amount ELSE 0 END) includes every non-matching row as a zero in the divisor. On the 2024-2025 aid year that turns a real average grant of about 10,309 dollars among Pell-eligible students into about 1,856 — off by a factor of five, with no error. If you wanted "average among students who matched," return NULL for the non-matches or use FILTER.

-- WRONG: the ELSE 0 rows are averaged in, so the answer is ~1,856
SELECT AVG(CASE WHEN fa_pell_eligible = 'Y' THEN fa_total_grants ELSE 0 END) AS avg_grant
FROM ods_financial_aid
WHERE fa_year = '2024-2025';

-- RIGHT: NULL out non-matches so AVG ignores them (~10,309)
SELECT AVG(CASE WHEN fa_pell_eligible = 'Y' THEN fa_total_grants END) AS avg_grant
FROM ods_financial_aid
WHERE fa_year = '2024-2025';

-- ALSO RIGHT: FILTER
SELECT AVG(fa_total_grants) FILTER (WHERE fa_pell_eligible = 'Y') AS avg_grant
FROM ods_financial_aid
WHERE fa_year = '2024-2025';

Practice these on QueryU

Conditional aggregation appears across the medium and hard tiers. Each challenge below tests one of the patterns above on the same dataset, and is graded on its result set — any valid query returning the right rows passes.