Group records by range

Question

I need to select a count of items that fall into some range like this

create table numbers (val int);

insert into numbers(val) values (2), (3), (11), (12), (13), (31);

select count(1) as qty , val / 10 as range
from numbers
group by val / 10;

Obviously, if there're no items in range, it won't be included in the output. I could think of some inelegant ways to include all ranges in the output, but is there an elegant and fast one (in PostgreSQL or MS SQL Server dialect)

Answer 1

It appears that you want to create a histogram of your results.

PostgreSQL:

select x, count(val)
from generate_series(1,6) x 
left outer join numbers on (x = width_bucket(val, 0, 60, 6))
group by x;

I've used width_bucket instead of a simple division and modulus because it's more general and easier to get right for more complex ranges. Also, it's awesome.

Mark Bannister's recursive CTE for sequence generation can be integrated and JOINed on as x instead of the generate_series for added portability if you want, and the limits can be automatically determined:

with recursive ranges(rangev) as (
    select 0 rangev union all select rangev+1 as rangev from ranges where rangev < 4
), bounds(lower_bucket, upper_bucket) as (
    select (min(val))/10, (max(val)/10)+1 from numbers
)
select
    rangev as bucket,
    rangev*10 AS lower_bound,
    (rangev+1)*10-1 AS upper_bound,
    count(val) AS num_in_bucket
from ranges cross join bounds
left outer join numbers on (rangev = width_bucket(val, lower_bucket, upper_bucket*10, upper_bucket))
group by rangev
order by rangev asc;

If you prefer /10 over width_bucket (say, if width_bucket isn't available in MS SQL) that's easy to change back.

Output:

 bucket | lower_bound | upper_bound | num_in_bucket 
--------+-------------+-------------+---------------
      0 |           0 |           9 |             0
      1 |          10 |          19 |             2
      2 |          20 |          29 |             3
      3 |          30 |          39 |             0
      4 |          40 |          49 |             1
(5 rows)