Conditional logic & CASE

Conditional aggregation, CASE evaluation order, CASE in GROUP/ORDER BY, FILTER clause.

• ★Conditional aggregation

  SUM(CASE WHEN ... THEN amount END) is the pivot workhorse; a missing ELSE means NULL (usually right — the 0-vs-NULL choice made explicit).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE sales(region VARCHAR, amount INT); INSERT INTO sales VALUES ('N',100),('S',200),('N',50);

  The trap

  SELECT COUNT(CASE WHEN region='N' THEN 1 ELSE 0 END) AS n FROM sales;

  3

  The fix

  SELECT SUM(CASE WHEN region='N' THEN 1 ELSE 0 END) AS n FROM sales;

  2

  The 'North sales count' reads 3, not 2 — COUNT counts the ELSE 0 rows too, because 0 is not NULL; SUM adds the flags instead.

• ★CASE without ELSE is NULL

  A CASE without ELSE yields NULL — which SUM ignores (usually what you want) and COUNT-of-CASE also ignores (which makes COUNT(CASE...) a conditional counter).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE att(member VARCHAR, kind VARCHAR); INSERT INTO att VALUES ('Ada','workshop'),('Ada','dropin'),('Ben','dropin');

  The trap

  SELECT member, COUNT(CASE WHEN kind = 'workshop' THEN 1 ELSE 0 END) AS workshops FROM att GROUP BY member ORDER BY member;

  Ada	2
  Ben	1

  The fix

  SELECT member, COUNT(CASE WHEN kind = 'workshop' THEN 1 END) AS workshops FROM att GROUP BY member ORDER BY member;

  Ada	1
  Ben	0

  The workshop count included the drop-in rows too — a CASE with ELSE 0 makes every row countable, where dropping the ELSE leaves the non-matches NULL and COUNT skips them.

• CASE in GROUP BY / ORDER BY

  A CASE expression can bucket rows in GROUP BY or order them in ORDER BY.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE chili(entry VARCHAR, heat INT); INSERT INTO chili VALUES ('a',9),('b',5),('c',1);

  The trap

  SELECT CASE WHEN heat >= 8 THEN 'blazing' WHEN heat >= 4 THEN 'toasty' ELSE 'cool' END AS tier, COUNT(*) AS n FROM chili GROUP BY tier ORDER BY tier;

  blazing	1
  cool	1
  toasty	1

  The fix

  SELECT tier, n FROM (SELECT CASE WHEN heat >= 8 THEN 'blazing' WHEN heat >= 4 THEN 'toasty' ELSE 'cool' END AS tier, COUNT(*) AS n, MIN(CASE WHEN heat >= 8 THEN 1 WHEN heat >= 4 THEN 2 ELSE 3 END) AS rk FROM chili GROUP BY tier) ORDER BY rk;

  blazing	1
  toasty	1
  cool	1

  The heat tiers printed blazing, cool, toasty — alphabetical, not the hottest-first podium — a CASE can bucket rows in GROUP BY and a rank-mapping CASE can order them, so the podium is blazing, toasty, cool.

• FILTER (WHERE ...)

  agg(...) FILTER (WHERE ...) is the cleaner dialect form of conditional aggregation.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE feed(otter VARCHAR, kind VARCHAR, g INT); INSERT INTO feed VALUES ('o1','fish',10),('o1','crab',5);

  The trap

  SELECT otter, SUM(CASE WHEN kind = 'fish' THEN g END) AS fish FROM feed GROUP BY otter;

  o1

  10

  The fix

  SELECT otter, SUM(g) FILTER (WHERE kind = 'fish') AS fish FROM feed GROUP BY otter;

  o1

  10

  Both spellings give the same fish grams — FILTER (WHERE …) wears the condition on the outside of the aggregate, identical semantics to the CASE form but clearer, and narrower in portability (Postgres/DuckDB yes; BigQuery/MySQL no).

• CASE takes the first match

  CASE takes the first matching WHEN — overlapping conditions must be ordered narrowest-first, or the wide arm swallows the boundary.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE batches(batch VARCHAR, cure_days INT); INSERT INTO batches VALUES ('b1',60),('b2',30),('b3',10);

  The trap

  SELECT batch, CASE WHEN cure_days >= 20 THEN 'standard' WHEN cure_days >= 45 THEN 'premium' ELSE 'quick' END AS grade FROM batches ORDER BY batch;

  b1	standard
  b2	standard
  b3	quick

  The fix

  SELECT batch, CASE WHEN cure_days >= 45 THEN 'premium' WHEN cure_days >= 20 THEN 'standard' ELSE 'quick' END AS grade FROM batches ORDER BY batch;

  b1	premium
  b2	standard
  b3	quick

  A 60-day batch graded 'standard' and the top 'premium' tier stayed empty — CASE takes the first matching WHEN, so the wide arm listed first swallowed the boundary.

• Simple CASE cannot match NULL

  Simple CASE compares with = so CASE x WHEN NULL never matches — the NULL arm is dead code and NULL rows fall to ELSE; only searched CASE sees them.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE reads(sensor VARCHAR, moisture INT); INSERT INTO reads VALUES ('s1',NULL),('s2',1);

  The trap

  SELECT sensor, CASE moisture WHEN NULL THEN 'missing' WHEN 1 THEN 'ok' ELSE 'other' END AS label FROM reads ORDER BY sensor;

  s1	other
  s2	ok

  The fix

  SELECT sensor, CASE WHEN moisture IS NULL THEN 'missing' WHEN moisture = 1 THEN 'ok' ELSE 'other' END AS label FROM reads ORDER BY sensor;

  s1	missing
  s2	ok

  The NULL moisture sensor was labelled 'other' via ELSE — simple CASE tests with =, and moisture = NULL is UNKNOWN, so the WHEN NULL arm is dead code; only a searched CASE with WHEN moisture IS NULL sees it.