sqlnetezza

SQL: Making Automatic Groups in a Variable


I am working with Netezza SQL.

I have a table (my_table) that looks like this:

   id      var1
1   1  43.95244
2   2  76.98225
3   3 255.87083
4   4 107.05084
5   5 112.92877
6   6 271.50650
7   7 146.09162
8   8 -26.50612
9   9  31.31471
10 10  55.43380

My Question: I want to create a new variable that groups var1 into groups of 100. Normally, I would do this with the CASE statement:

select *,
case
 when var1 >-100 and var1 <= 0 then '-100 to 0'
 when var1 >0 and var1 <= 100 then '0 to 100'
 when var1 > 100 and var1 <= 200 then '100 to 200'
 when var1 >200 and var1 <= 300 then '200 to 300'
end as new_var
from my_table

   id      var1    new_var
1   1  43.95244   0 to 100
2   2  76.98225   0 to 100
3   3 255.87083 200 to 300
4   4 107.05084 100 to 200
5   5 112.92877 100 to 200
6   6 271.50650 200 to 300
7   7 146.09162 100 to 200
8   8 -26.50612  -100 to 0
9   9  31.31471   0 to 100
10 10  55.43380   0 to 100

I am trying to do this in a more automatic fashion that will not require manually writing all these groups.

I tried to use the FLOOR statement to accomplish this:

select *, (FLOOR(var1/100)*100 || 'to' || (FLOOR(var1/100)*100 +100)) as new_var from my_table

But the result does not look correct:

   id      var1 new_var
1   1  43.95244      0
2   2  76.98225      0
3   3 255.87083    200
4   4 107.05084    100
5   5 112.92877    100
6   6 271.50650    200
7   7 146.09162    100
8   8 -26.50612   -100
9   9  31.31471      0
10 10  55.43380      0

Can someone please show me how to do this correctly?

Thanks!


Solution

  • DROP TABLE IF EXISTS stuff;
    create table stuff (id int, value DOUBLE);
    
    INSERT INTO STUFF valueS(1, 43.95244);
    INSERT INTO STUFF valueS(2, 76.98225);
    INSERT INTO STUFF valueS(3, 255.87083);
    INSERT INTO STUFF valueS(4, 107.05084);
    INSERT INTO STUFF valueS(5, 112.92877);
    INSERT INTO STUFF valueS(6, 271.50650);
    INSERT INTO STUFF valueS(7, 146.09162);
    INSERT INTO STUFF valueS(8, -26.50612);
    INSERT INTO STUFF valueS(9,  31.31471);
    INSERT INTO STUFF valueS(10,  55.43380);
    
    SELECT *, floor(value/100) as slice FROM STUFF
    order by slice;
    
    id value slice
    8 -26.50612 -1
    1 43.95244 0
    2 76.98225 0
    9 31.31471 0
    10 55.4338 0
    4 107.05084 1
    5 112.92877 1
    7 146.09162 1
    3 255.87083 2
    6 271.5065 2
    SELECT *, 100 * floor(value/100) as from_value, 100 * (floor(value/100) + 1) as to_value FROM STUFF
    order by from_value;
    
    id value from_value to_value
    8 -26.50612 -100 0
    1 43.95244 0 100
    2 76.98225 0 100
    9 31.31471 0 100
    10 55.4338 0 100
    4 107.05084 100 200
    5 112.92877 100 200
    7 146.09162 100 200
    3 255.87083 200 300
    6 271.5065 200 300