Deviopath › PostgreSQL

Score Histogram with Empty Buckets

Hard · Grouping

A histogram counts values per range ("bucket"). Integer division puts a score in a bucket: score / 10 is 3 for every score from 30 to 39. Grouping only returns buckets that have rows, so to show empty buckets too, generate every bucket with generate_series and LEFT JOIN the data to it.

Count the students per score bucket of width 10: 0-9, 10-19, ..., 80-89, and a last bucket 90-100 that also holds the perfect score 100. Students with a NULL score are not counted. Every one of the 10 buckets must appear, with 0 when empty.

Return bucket (the label text, like 30-39) and students, sorted from the lowest bucket to the highest.

Next: Quartiles per Class