Question Re: Sql help on creating ranges in Technology Q&A

Sql help on creating ranges

Former Member — Wed, 07 Sep 2011 20:26:08 GMT

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

Re: Sql help on creating ranges

Former Member — Wed, 07 Sep 2011 21:47:47 GMT

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

Re: Sql help on creating ranges

VolkerBarth — Thu, 08 Sep 2011 03:14:15 GMT

This FAQ contains a similar question - take a look:)

Re: Sql help on creating ranges

Former Member — Thu, 08 Sep 2011 10:44:40 GMT

How about just doing a query for each range and UNIONing the result sets?

SELECT '0 - 10', COUNT() FROM mytable WHERE mytable.value BETWEEN 0 AND 10 UNION SELECT '11 - 20', COUNT() FROM mytable WHERE mytable.value BETWEEN 11 AND 20 UNION (and so on...)

Of course, this simple approach will only be realistic if you have a finite, and relatively small, number of ranges you want to report.

Another thought would be to write a procedure that loops through ranges that increment each pass, essentially just automating my simple-minded UNION idea.

Re: Sql help on creating ranges

Former Member — Thu, 08 Sep 2011 10:50:31 GMT

Just looked at the FAQ referenced by Volker. I bow to its elegance. Regarding my own suggestion, "Never mind!"