PatternSQL
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
Pattern
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.