Bucket values into ranges
Turn a continuous column into a histogram: band it into ranges with CASE WHEN, then GROUP BY the bucket label and COUNT.
Last updated
SELECT
CASE
WHEN order_total < 100 THEN '0-99'
WHEN order_total < 500 THEN '100-499'
WHEN order_total < 1000 THEN '500-999'
ELSE '1000+'
END AS bucket,
COUNT(*) AS orders
FROM orders
GROUP BY bucket
ORDER BY MIN(order_total);Why it’s written this way
CASE evaluates its WHEN clauses in order and stops at the first one that's true, so listing the thresholds from smallest to largest is what makes '< 500' correctly mean 'between 100 and 499' — it's only reached once '< 100' has already failed. The result is a text label standing in for a numeric range.
GROUP BY bucket then works exactly like grouping by any other column: every row that produced the same label collapses into one count. ORDER BY MIN(order_total) sorts the output by the buckets' real numeric position instead of alphabetically, which would otherwise put '1000+' ahead of '500-999'.
Edge cases to know
- →The bucket edges are hardcoded — adding a new price tier means editing the CASE expression by hand, not something the query discovers on its own. WIDTH_BUCKET(), where an engine supports it, computes bucket numbers from a min, max and count instead.
- →A NULL order_total falls through every WHEN and lands in ELSE ('1000+') as written here, which is almost certainly wrong — add a leading WHEN order_total IS NULL THEN 'unknown' if NULLs are expected in the data.
- →GROUP BY bucket relies on grouping by an alias, which DuckDB and Postgres allow but not every engine does — those instead need the full CASE expression repeated verbatim in the GROUP BY clause.
Part by part
- CASE
- Evaluated top to bottom, stopping at the first match. That ordering is what lets each branch omit the lower bound the previous one already covered.
- WHEN order_total < 500 THEN '100-499'
- Reached only when the first branch failed, so the implied lower bound is 100. Reordering these branches silently changes every bucket.
- ELSE '1000+'
- The catch-all. Without it, values above the last threshold produce NULL and form an unlabelled group in the output.
- ORDER BY MIN(order_total)
- Sorts buckets by their contents rather than by label, which is what stops '1000+' appearing between '0-99' and '100-499' alphabetically.
Common mistakes
ORDER BY bucketSorts the labels as text, so '1000+' comes second and the histogram reads out of order. Sorting by an aggregate of the underlying value keeps the buckets in numeric sequence.
ORDER BY MIN(order_total)WHEN order_total < 100 THEN '0-99' WHEN order_total < 1000 THEN '500-999'A gap in the thresholds means values between 100 and 499 fall into the 500-999 bucket, whose label is now a lie. The branches must be contiguous as well as ordered.
Omitting the ELSE branchRows above the highest threshold get NULL as their bucket and appear as an unnamed group. Whether that is visible depends on the chart, which is how a whole tail of large orders goes missing.
Questions
Why does branch order matter so much?
Because CASE stops at the first true branch. Written ascending, each branch only needs its upper bound; written in any other order, values fall into the wrong bucket while every branch still looks correct in isolation.
How do I make the buckets even automatically?
Divide and floor: FLOOR(order_total / 100) * 100 gives fixed-width bins with no CASE at all. It is far less code, and it cannot express the uneven bands that usually match how a business thinks.
How do I show buckets with no rows?
Define the buckets in their own table or a VALUES list and LEFT JOIN the counts onto it. GROUP BY alone can only produce groups that have data, so empty bands are absent rather than zero.
Should the bounds be inclusive or exclusive?
Pick one and label consistently. Using < throughout, as here, means each bucket excludes its upper bound — so a value of exactly 100 belongs to the second band, which the label should make obvious.