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's gravatar image

Sybill
30112
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
permanent link

answered 07 Sep '11, 21:47

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

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

permanent link

answered 08 Sep '11, 03:14

Volker%20Barth's gravatar image

Volker Barth
31.5k321462677
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.

permanent link

answered 08 Sep '11, 10:44

Seth_Krieger's gravatar image

Seth_Krieger
116127
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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: 978 times

last updated: 08 Sep '11, 11:07