Lessons · SQL · sorting rows into buckets

Turning values into labels

CASE WHEN amount >= 200 THEN 'large' ELSE 'small' END turns a number into a label inside the query, so you can group and count by the label.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Reports speak in buckets: small, medium, large; new, returning; on time, late. CASE is how the raw column becomes the category the reader wants.

How to think about it

Write the buckets from the most specific to the least, because CASE takes the first WHEN that is true. Put the CASE in a CTE or repeat it in GROUP BY; then COUNT per bucket.

Worked example

SELECT CASE WHEN amount >= 200 THEN 'large' WHEN amount >= 50 THEN 'medium' ELSE 'small' END AS size, COUNT(*)

First matching WHEN wins, so the biggest threshold comes first.

FROM orders

The rows.

GROUP BY size;

One row per bucket.

Your turn

Label films as long or short at 120 minutes.

SELECT CASE WHEN minutes >= 120 THEN 'long'  'short' END AS length FROM films;

The trap

Without ELSE, rows that match no WHEN get NULL, and that NULL becomes its own silent bucket in the GROUP BY.

Practise sorting rows into buckets on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.