2

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? – dfundako Sep 20 '16 at 15:34
  • Sure, i was editing in fact adding the info you requested. – xCloudx8 Sep 20 '16 at 15:38
  • Why doesn't the first range start with 0? – a_horse_with_no_name Sep 20 '16 at 16:50
  • The range starts from the min(pos), but it can also starts from 0 – xCloudx8 Sep 21 '16 at 7:10
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.