Lesson 20.4

Building a Histogram in SQL

A histogram is a GROUP BY on a rounded-down value. FLOOR does the rounding, and a generated range keeps the bands that came up empty.

A median and a standard deviation summarize a distribution in a number or two. A histogram shows its whole shape: split the values into equal-width bands and count the rows in each. In SQL that is a GROUP BY on a value rounded down to the start of its band.

Pattern

select floor(price / 10) * 10 as price_band,
       count(*) as products
from products
group by price_band
order by price_band

Rounding down to a band

floor(price / 10) counts how many whole $10 steps fit below the price, and multiplying by 10 turns that back into dollars. A $13.50 product lands in the band starting at 10, and so does a $19.99 one. Change both 10s to change the width. Use floor, not round: rounding would put $16 in the band starting at 20.

Bands of your own choosing

When the bands should be uneven, or need readable names, write them out with the CASE bucketing from Lesson 16.4 · Distribution with CASE buckets. Sorting by min(price) keeps the labels in price order rather than alphabetical order:

Named, uneven bands

select case
         when price < 10 then 'under $10'
         when price < 20 then '$10 to $19.99'
         else '$20 and up'
       end as price_band,
       count(*) as products
from products
group by price_band
order by min(price)

The bands that came up empty

A GROUP BY only returns bands that contain a row. A chart built from it skips the empty ones and looks smoother than the data. To keep them, generate every band and left join the rows to it. range(0, 34, 2) is DuckDB's list of band starts; PostgreSQL calls it generate_series:

$2 bands, empty ones included

select bands.band_start,
       count(p.product_id) as products
from range(0, 34, 2) as bands(band_start)
left join products as p
  on floor(p.price / 2) * 2 = bands.band_start
group by bands.band_start
order by bands.band_start

The bands starting at 0, 4, 26, and 30 come back with 0. Nothing costs between $4 and $6, and a plain GROUP BY would have closed the gap without a trace. Counting p.product_id rather than * is what turns an empty band into 0: the left join gives it one row of NULLs, and count(*) would count that row as 1.

DuckDB's histogram()

DuckDB also has a histogram() aggregate that returns the whole count in one cell, as a map from each value to its count. It is handy for a quick look; the GROUP BY version is the one that ports to other databases and feeds a chart.

One cell, every band

select histogram(floor(price / 10) * 10) as price_bands
from products

Schema · Garden Shop Tables · orders, order_items 2 tables

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Syntax reference

Need syntax details? See the SQL statistical aggregate reference

Your task

Build a histogram of order totals in $25 bands. The starter already computes each order's total. Return band_start, band_end (band_start plus 25), and orders (the number of orders whose total falls in the band). Sort by band_start.