CODEWITHSUNDEEP
sqlsybase
limitingfactor
limitingfactor

Reputation: 77

SQL: Calculate the interval a value falls into

I have a column like this:

column1   
      4
     12
     23
      6
     14
     35

I want to create another column next to it that will show the range that the value from column1 falls into, like this:

column1        column2  
      4            0-5
     23          21-25 
      6           6-10
     14          11-15
     33          31-35

It seems to be a simple ranking task, but I'm having trouble making it work. I am pretty new to SQL and probably am missing something basic. So maybe just point a direction for me to go from where I am now, because I am lost.

So far I have looked into using CASE (with no success) and RANK(). The latter doesn't work for me at all, as in it's not even highlighted as a keyword (I am using Sybase Interactive SQL v12.0.1). Although it looks to me that RANK() is not the way to go even if it worked, since I need my values ranked in precise ranges (0 to 35 with step 5).

Thank you all in advance!

Upvotes: 0

Views: 159

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272236

Assuming you want to group values inside 0-5 (special case), 6-10, 11-15 and so on, just use the following query (written for SQL Server but illustrates the idea):

SELECT num, CONCAT(
    CASE WHEN num <= 5 THEN 0 ELSE FLOOR((num - 1) / 5) * 5 + 1 END,
    '-',
    CASE WHEN num <= 5 THEN 5 ELSE FLOOR((num - 1) / 5) * 5 + 5 END
)
FROM ...

Tests on DB Fiddle

Replace FLOOR(x / y) with integer division operator if available.
Replace CONCAT with appropriate string concatenation operator or function.

Upvotes: 1

C.E James
C.E James

Reputation: 335

select col1, concat( floor(col1 / 5) * 5, '-', 5 + floor(col1 / 5) * 5)

Upvotes: 0

koltorex
koltorex

Reputation: 64

or concat(column1-(column1%5), "-", column1-(column1%5)+5)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can do something like this:

select col1,
       concat( floor(col1 / 5) * 5, '-', 5 + floor(col1 / 5) * 5)

You can also use the '||' operator:

select col1,
       floor(col1 / 5) * 5 || '-' || (5 + floor(col1 / 5) * 5))

Or whatever method your database uses for string concatenation.

Upvotes: 3

Related Questions