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