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:

So the result should be:

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

Some sample data please? Either a sql fiddle or create and insert statements. — Lalit Kumar B, Jul 28 '15 at 05:20
Sample data means simply i can give random person_id and age for that person. What else you need? — Madhusudan, Jul 28 '15 at 05:22
Sample data means post the data so that someone can use it to build the query. Post few rows, i.e. create table and insert statements. — Lalit Kumar B, Jul 28 '15 at 05:24

score 3 · Accepted Answer · answered Jul 28 '15 at 05:33

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)

score 2 · Answer 2 · answered Jul 28 '15 at 05:38

You may want to do something like this:

with data as (
  select p.person_id,TRUNC ((SYSDATE - p.birth_date) / 365.25, 0) AS age
  from person p
)
select '30 or below' as stat, sum(case when age between 0 and 30 then 1 else 0 end) as count from data
union all
select '31 to 60' as stat, sum(case when age between 31 and 60 then 1 else 0 end) as count from data
union all
select '31 to 130' as stat, sum(case when age between 31 and 130 then 1 else 0 end) as count from data
;

Example: http://sqlfiddle.com/#!4/8c9ae4/13

score 1 · Answer 3 · answered Jul 28 '15 at 05:38

Assuming that you already have calculated values for age. You could use CASE expression and GROUP BY different age groups.

For example,

SQL> WITH data(person_id, age) AS(
  2  SELECT 1,  3 FROM dual UNION ALL
  3  SELECT 2,  4 FROM dual UNION ALL
  4  SELECT 3,  8 FROM dual UNION ALL
  5  SELECT 4,  9 FROM dual UNION ALL
  6  SELECT 5, 15 FROM dual UNION ALL
  7  SELECT 6, 25 FROM dual UNION ALL
  8  SELECT 7, 32 FROM dual UNION ALL
  9  SELECT 8, 44 FROM dual UNION ALL
 10  SELECT 9, 67 FROM dual UNION ALL
 11  SELECT 10, 75 FROM dual
 12  )
 13  SELECT (
 14    CASE
 15      WHEN age <= 5
 16         THEN 'age <= 5'
 17      WHEN age > 5 AND age <= 10
 18         THEN 'age > 5 and age <= 10'
 19      WHEN age > 10 AND age <= 30
 20         THEN 'age > 10 and age <= 30'
 21      ELSE 'age > 30'
 22    END) age_group,
 23    COUNT(*)
 24  FROM data
 25  GROUP BY (
 26    CASE
 27      WHEN age <= 5
 28         THEN 'age <= 5'
 29      WHEN age > 5 AND age <= 10
 30         THEN 'age > 5 and age <= 10'
 31      WHEN age > 10 AND age <= 30
 32         THEN 'age > 10 and age <= 30'
 33      ELSE 'age > 30'
 34    END)
 35  /

AGE_GROUP                COUNT(*)
---------------------- ----------
age <= 5                        2
age > 10 and age <= 30          2
age > 5 and age <= 10           2
age > 30                        4

SQL>

If you don't have a range of age group, then you could use DECODE to hard code the exact age.

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

3 Answers3

Linked