How to get count of people based on age groups using SQL query in Oracle database?

Question

Suppose I have a following query (which a part of my actual query) which retrieves person_id and age for that person:

select p.person_id,TRUNC ((SYSDATE - p.birth_date) / 365.25, 0) AS age
from person p

Now after getting this age I want to get the number of people in each age group like:

 group                count
age <=5                100
age >5 and age <=10    50
 age > 10              15
   ...                ...

I can use group by clause to get count for individual age. But how to get count between two ages?

Can someone tell me the way in which I can do it?

you can use following data for your reference:

   pid    age
    1      4
    2      13
    3      10
    4      8
    5      9 
    6      12

So the result should be:

   group      count
   age < =5     1
   >5 & <=10    3
   > 10         2

Answer 1

First, your age calculation is incorrect. You almost certainly want to measure the months between the two dates rather than hoping that dividing by 365.25 is close enough

trunc( months_between( sysdate, p.birth_date )/ 12 )

Second, if you want to group by ranges, you just need to select the range in a case statement and group by that

SELECT (case when age <= 5
             then 'age <= 5'
             when age > 5 and age <= 10
             then 'age > 5 and age <= 10'
             else 'age > 10'
         end) bucket,
       count(*)
  FROM( SELECT trunc( months_between( sysdate, p.birth_date )/ 12 ) age
          FROM person p )
 GROUP BY (case when age <= 5
                then 'age <= 5'
                when age > 5 and age <= 10
                then 'age > 5 and age <= 10'
                else 'age > 10'
            end)