The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

# Sql help on creating ranges

 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 asked 07 Sep '11, 20:26 Sybill 30●1●1●2 accept rate: 0%

 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 ``` answered 07 Sep '11, 21:47 Glenn Paulley 10.7k●5●68●104 accept rate: 43%
 This FAQ contains a similar question - take a look:) answered 08 Sep '11, 03:14 Volker Barth 29.3k●287●438●645 accept rate: 32%
 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. answered 08 Sep '11, 10:44 Seth_Krieger 116●1●2●7 accept rate: 25% Just looked at the FAQ referenced by Volker. I bow to its elegance. Regarding my own suggestion, "Never mind!" (08 Sep '11, 10:50) Seth_Krieger
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×90
×5

question asked: 07 Sep '11, 20:26

question was seen: 775 times

last updated: 08 Sep '11, 11:07