Histogram Buckets in SQL

Example query

This example assumes: orders(id, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT FLOOR(total_amount / 50) * 50 AS bucket_start,
       COUNT(*) AS orders
FROM orders
WHERE total_amount >= 0
GROUP BY 1
ORDER BY 1;

Fixed number of buckets with WIDTH_BUCKET

SELECT WIDTH_BUCKET(total_amount, 0, 500, 10) AS bucket,
       MIN(total_amount) AS lowest,
       MAX(total_amount) AS highest,
       COUNT(*) AS orders
FROM orders
WHERE total_amount >= 0
GROUP BY 1
ORDER BY 1;

How it works

• Dividing by the bucket width, flooring and multiplying back gives the start of the range each value falls in (0, 50, 100 and so on).
• Grouping by that start and counting gives the height of each bar.
• WIDTH_BUCKET(value, low, high, n) splits the range into n equal buckets; values outside the range land in bucket 0 or n + 1.

MySQL, BigQuery, SQL Server and SQLite differences

• WIDTH_BUCKET is PostgreSQL, Oracle and Snowflake. In MySQL, SQL Server, SQLite and BigQuery use the FLOOR version (in SQL Server, divide a decimal so it does not integer-divide).

Common mistakes

• A few extreme values stretch the buckets so most data lands in one bar; cap the range or use a log scale.
• Empty buckets are missing from the output, which can make a histogram look shorter than it is.
• Bucket width changes the shape; try two widths before drawing a conclusion.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Show a histogram of order values in buckets of 50.
• How are ticket resolution times distributed across 4-hour ranges?