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]

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.

[Former Member]

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