# Create range from column

I have a list of positions:

``````pos
123
100024
1025
5426
``````

I want to define a range of these positions

E.g.

100-1000

1001-2500

etc...

So when i do a Select of how many positions there are in that range counts the number of elements in the range.

I've tried with:

``````SELECT pos , generate_seres(max(pos)::int, min(pos)::int, 1000::int)
as pos_int
FROM varianti
group by pos
ORDER BY pos;
``````

But i don't think that is correct...

Is there a way?

• Can you show us what you have tried and found to not work? Sep 20 '16 at 15:34
• Sure, i was editing in fact adding the info you requested. Sep 20 '16 at 15:38
• Why doesn't the first range start with `0`? Sep 20 '16 at 16:50
• The range starts from the min(pos), but it can also starts from 0 Sep 21 '16 at 7:10

If the first range is allowed to start with `0`, you can do something like this:

``````with ranges (range) as (
select int4range(i, i + 1000, '[)')
from generate_series(0, (select max(pos) from varianti), 1000) as i
)
select range,
count(v.pos) as range_count
from ranges l
left join varianti v
on v.pos <@ l.range
group by range
order by range;
``````

For this data:

``````insert into varianti (pos)
values
(123), (150), (170),
(1025), (1050), (1051), (1052), (1053), (1054), (1054),
(2054), (2051),
(3001), (3002), (3003), (3004), (4004),
(5426);
``````

This returns:

``````range       | range_count
------------+------------
[0,1000)    |           3
[1000,2000) |           7
[2000,3000) |           2
[3000,4000) |           4
[4000,5000) |           1
[5000,6000) |           1
``````