Script categories

Thursday, 22 September 2011

Calculating Age and grouping into Age groups

-- Grouping people in a specific age group
with t as
(
select 'john' name,to_date('01.01.1990','dd.mm.yyyy') birth_date from dual union
select 'sue',      to_date('02.05.2000','dd.mm.yyyy')  from dual union
select 'abraham',  to_date('04.07.1998','dd.mm.yyyy')  from dual union
select 'Tim',      to_date('15.09.1998','dd.mm.yyyy')  from dual union
select 'Rob',      to_date('16.09.1998','dd.mm.yyyy')  from dual union
select 'Mary',     to_date('14.09.1990','dd.mm.yyyy')  from dual union
select 'Larry',    to_date('16.09.1995','dd.mm.yyyy')  from dual union
select 'Sally',    to_date('16.09.1965','dd.mm.yyyy')  from dual
)
--
select age_group , count(*)
from
(
  select
    case
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 17 then
        '0-17'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 24 then
        '18-24'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 34 then
        '25-34'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 44 then
        '35-44'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 54 then
        '45-54'
      else 'n/a'
    end age_group
  from t
)
group by age_group
order by age_group;

No comments:

Post a Comment