CASE expressions and buckets

Grouping only works on categories that exist. The sales file has region and channel and product, so you can group by those. It does not have a column saying whether an order was small, medium or large, and that is often exactly the question. CASE is how you invent the column, in the query, without touching the file.

It is the if-then-else of SQL, and it is an expression rather than a statement: it produces a value, so it can go anywhere a value can go. In the select list. Inside an aggregate. In an ORDER BY. That flexibility is what makes it the most quietly powerful thing in this course.

The searched form

The version in the editor above is the general one: a ladder of conditions, each with a result, and a fallback at the bottom.

CASE
  WHEN revenue >= 5000 THEN 'large'
  WHEN revenue >= 1000 THEN 'medium'
  ELSE 'small'
END AS size

The conditions are tested top to bottom and the first true one wins. That ordering is not a detail, it is the whole design. A row with revenue of six thousand matches both the first and second conditions, and it is called large because large is written first. Write the ladder from most restrictive to least and it reads correctly; write it the other way round and every row lands in the first band.

The ELSE is optional, and leaving it out is the most common CASE bug. A row matching no condition gets NULL, which then travels through your GROUP BY as a blank category and through any arithmetic as a null result. Always write the ELSE, even if it is only ELSE 'other', because a visible "other" bucket with an unexpected count in it is a finding, and a silent null is a bug.

The simple form

When every branch tests the same column for equality, there is a shorter spelling:

CASE region
  WHEN 'North' THEN 'Domestic'
  WHEN 'South' THEN 'Domestic'
  ELSE 'Other'
END AS bucket

This is a mapping table written inline, and it is how you collapse a dozen raw status codes into three that a human can read. Its one limitation is that it only does equality: the moment a branch needs a range or a LIKE, switch back to the searched form.

For pure equality mapping DuckDB and Postgres also give you COALESCE, NULLIF and IF, which are shorter for the specific jobs they do. CASE is the one that always works.

Grouping by a bucket

Once the expression is aliased, grouping on it is ordinary GROUP BY:

SELECT
  CASE WHEN revenue >= 5000 THEN 'large'
       WHEN revenue >= 1000 THEN 'medium'
       ELSE 'small' END AS size,
  COUNT(*) AS orders
FROM csv
GROUP BY size

Three rows out, and you have just built a histogram of order sizes on a file that has no order size column. Grouping by the alias is a DuckDB and Postgres convenience; some databases make you repeat the entire CASE expression in the GROUP BY, which is the strongest possible argument for writing the query as a CTE instead, as in lesson 14.

One wrinkle: your buckets come back in alphabetical order unless you say otherwise, so large, medium, small rather than small, medium, large. Fixing that means sorting on something other than the label, usually a second CASE producing a sort number, or the lower bound of the band.

CASE inside an aggregate

This is the technique worth stealing. Put a CASE inside a SUM and you total one slice of the data while an ordinary SUM beside it totals everything:

SELECT
  region,
  SUM(CASE WHEN channel = 'Online' THEN revenue ELSE 0 END) AS online_revenue,
  SUM(revenue) AS total_revenue
FROM csv
GROUP BY region

Two different populations, one pass, one row per region, and the ratio between the columns is a share that you could not get from two separate queries without joining them back together. This pattern is called conditional aggregation, and it is how nearly every real pivot table is built underneath.

Note the ELSE 0. With SUM it is interchangeable with leaving it out, since SUM ignores nulls anyway. With COUNT it matters enormously: COUNT(CASE WHEN ... THEN 1 ELSE 0 END) counts every row, because zero is a value. You want ELSE NULL there, or better, the FILTER clause from the end of lesson 9.

What to look up next

Look up the PIVOT statement in DuckDB. Once you have written conditional aggregation by hand a few times, you will appreciate a single keyword that turns the distinct values of a column into columns of their own, and you will also understand exactly what it is doing for you.