Oracle: how to "group by" over a range?

Question

If I have a table like this:

pkey   age ----   ---    1     8    2     5    3    12    4    12    5    22 

I can "group by" to get a count of each age.

select age,count(*) n from tbl group by age; age  n ---  -   5  1   8  1  12  2  22  1 

What query can I use to group by age ranges?

  age  n -----  -  1-10  2 11-20  2 20+    1 

I'm on 10gR2, but I'd be interested in any 11g-specific approaches as well.

Answer 1

SELECT CASE           WHEN age <= 10 THEN '1-10'           WHEN age <= 20 THEN '11-20'           ELSE '21+'         END AS age,         COUNT(*) AS n FROM age GROUP BY CASE             WHEN age <= 10 THEN '1-10'             WHEN age <= 20 THEN '11-20'             ELSE '21+'           END 

Answer 2

Try:

select to_char(floor(age/10) * 10) || '-'  || to_char(ceil(age/10) * 10 - 1)) as age,  count(*) as n from tbl group by floor(age/10);