nlqdb

Solve · Analysts and PMs

How do I group numbers into ranges (buckets) in SQL?

If you want a count per range — orders under $50, $50–$200, over $200 — instead of one row per exact value, ask in plain English. nlqdb compiles the bucket expression, GROUP BYs the bucket, orders the ranges by their lower bound, and shows the SQL, so 5–10 sorts before 10–20 instead of after.

Bucketing a continuous column into ranges — a revenue histogram, orders by price band, users by age group — is the analyst's everyday question that plain `GROUP BY amount` gets wrong: it returns one row per exact value, not per band. You reach for a `CASE WHEN amount < 50 THEN ... END` label (or `width_bucket` for even bins) and group by that. Then two traps bite: the boundaries have to tile without a gap or overlap (`< 50` then `>= 50`, never `<= 50` then `>= 50`), and the bucket labels are text, so `ORDER BY bucket` sorts them alphabetically — '10-20' lands before '5-10' unless you order by the numeric lower bound.

The snippet that solves it.

> count of orders bucketed by amount: under 50, 50 to 200, and over 200

What nlqdb does for this

  • Ask 'count of orders bucketed by amount: under 50, 50 to 200, over 200'; nlqdb compiles the `CASE`-band bucket expression and runs it in Postgres.
  • It groups by the bucket, not the raw value, so you get one row per range instead of one per exact amount.
  • The compiled bands tile without a gap or overlap at the boundaries, so no row is dropped or double-counted between adjacent ranges.
  • Ranges come back ordered by their lower bound, not alphabetically, and every answer shows the SQL under a trace toggle (`SK-WEB-005`) to confirm the boundaries.

Drop into any HTML page

<nlq-data goal="count of orders bucketed by amount: under 50, 50 to 200, and over 200"></nlq-data>

The histogram query you'd otherwise hand-write — the CASE bands, the group-by-the-band, and the sort-by-lower-bound — is one English goal here, with the SQL shown so you can check the boundaries tile cleanly.

What this replaces

What nlqdb doesn't try to do here

  • The bucket edges are ones you name — 'under 50, 50 to 200, over 200'. nlqdb compiles the bands from your English and shows them; it won't pick a 'good' bin width for you.
  • A one-off read-only histogram, not a live chart that refreshes itself — your dashboard or embed re-asks on its own schedule.
  • The public `<nlq-data>` embed is read-scoped — it reports the bucketed counts, it doesn't write. Loading the rows goes through the SDK or `POST /v1/run`.

Questions buyers ask

How do I group numbers into ranges (buckets) in SQL?
Ask in plain English — 'count of orders bucketed by amount: under 50, 50 to 200, over 200.' nlqdb compiles a `CASE WHEN amount < 50 THEN ... END` bucket label, groups by that label instead of the raw amount, and orders the ranges by their lower bound. It runs the query in Postgres and shows the SQL, so you can confirm the bands tile without a gap.
Why does GROUP BY give me one row per value instead of per range?
Because `GROUP BY amount` groups by the exact value, so every distinct amount is its own group. To get bands you group by a derived bucket — a `CASE` expression that maps each amount to a range label (or `width_bucket` for even-width bins) — so all amounts in the same band collapse into one row.
Why do my range buckets sort 10-20 before 5-10?
Because the bucket labels are text, and `ORDER BY bucket` sorts text alphabetically — '1' sorts before '5', so '10-20' lands before '5-10'. Order by the numeric lower bound of each band (or by the `CASE`/`width_bucket` index) instead of the label. nlqdb compiles the numeric ordering and shows it in the SQL.
Can I bucket numbers on a Postgres database I already run?
Yes — connect it with the signed-in BYO connect verb (`nlq db connect`, `SK-DBCONN-001`; see /solve/query-existing-postgres-in-natural-language) and ask for the histogram in place, no ETL into a separate store. The honest limits: BYO connect is signed-in only (not the public embed), and the answer is read-only — nlqdb reports the buckets, it doesn't materialise them into a table.

Where this pain shows up in public

Enduring discussion hubs where you can verify the theme without taking our word for it. We don't quote individual posts; we cite search-result and subreddit URLs that stay live as new threads land.

Try nlqdb in 30 seconds

No sign-in. The anonymous database lasts 72 hours; adopt it with one click if you keep it.

Start with a goal →

Looking at this from a different angle? Browse all solve pages or browse competitor comparisons.