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!
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 |