CODEWITHSUNDEEP
sqlpostgresqlselectrangeintervals
Polly
Polly

Reputation: 1097

PostgreSQL make data fall into intervals with a step of 5

I have a data from 1 to N, and what I need to do is to make it fall into intervals with a step of five. For example every item from 1 to 5 goes to interval 1, from 6 to 10 into interval 2 and so on till the end.

I made this to find the intervals, but every new number seems to have its own interval. Can anyone help? 

select distinct Meters, (Meters/5)*5 || '-' || (Meters/5)*5+5 as Meters_range
from v_temp_schema.tmp_split_data
order by 1

The output:

     Meters  Interval 
        1    1-6     
        2    2-7     
        3    3-8     
        4    4-9     
        9    9-14
        6    6-11
        11   11-16
        8    8-13
        34   34-39
        .........
        78   78-83
        15   15-20
        81   81-86

The output I need:

     Meters  Interval 
        1    1-5     
        2    1-5     
        3    1-5     
        4    1-5     
        9    6-10
        6    6-10
        11   11-15
        8    6-10
        34   31-35
        .........
        78   76-80
        15   11-15
        81   81-85

Upvotes: 1

Views: 310

Answers (2)

Mureinik
Mureinik

Reputation: 311348

From the output you shared, it seems that the meters / 5 division is done as floating point division, so dividing and then multiplying by 5 just returns the same value. You could floor the result to make it act like integer division:

SELECT   DISTINCT meters, FLOOR(meters/5) * 5 || '-' || FLOOR(meters/5) * 5 + 5 AS Meters_range
-- Here ------------------^-----------------------------^
FROM     v_temp_schema.tmp_split_data
ORDER BY 1

Upvotes: 1

AlexYes
AlexYes

Reputation: 4208

there is a chance your meters column is not integer, that's why integer division didn't work, you have to make sure it is an integer... this query returned a correct result for me (I have just generated a series of integers to simulate meters values, make sure you have ::int part next to meters either in your formula or in a subquery 

select
 meters
,(meters/5)*5+1 || '-' || (meters/5)*5+5 as interval
from (
    select generate_series (1,100,1)::int as meters
) m

Upvotes: 2

Related Questions