Sql help on creating ranges

[Former Member]

I have a table with 2 columns(name and value). The values can range from -5000 to +5000. How do I write a query to get the count of values in ranges like Range Count ___
___ 0-10 4 10-20 8 20-30 400

[Former Member]

You need to come up with a mathematical function that provides a mapping between the values in the table to a different value that you can use as a grouping attribute in a GROUP BY clause. The simpler the function, the better - if you have to resort to a SQL user-defined function, it can be done and will work but the performance penalty for invoking the function so many times may be more than you desire. The mechanics of the function will depend a great deal on the possible ranges of values.

If, for example, you want to group the rows by ranges of 10, then you can do something like this:

create table bar (x integer);
insert into bar (x) select row_num from sa_rowgenerator( 1, 200, 1);
commit;
select floor(x/10) as fn, count(*)
from bar
group by fn
order by fn