## Question and Answer

## You Asked

Hi there

I am just a newbie at this so there is probably an easy solution to this problem. Here is my code . I am trying to get the total of males, females and unknowns in each agerange. At the moment for each person i am getting a line i of output

Agerange Male Female Unknown

30-39 1 0 0

30-39 0 1 0

and so on. All i want to get is a total for each age range

Thank you in Advance

select

sum(decode(gender,'M',1,0)) "males",

sum(decode(gender,'F',1,0)) "females",

sum(decode(gender,Null, 1,0)) "Unknown",

date_of_birth, tp.position,

months_between (sysdate, date_of_birth) / 12 as age_in_years,

round (months_between (sysdate, date_of_birth) / 12) as age,

case

when months_between (sysdate, date_of_birth) / 12 BETWEEN 20 AND 29.5 then '20-29'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 30 AND 39.5 THEN '30-39'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 40 AND 49.5 THEN '40-49'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 50 AND 59.5 THEN '50-59'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 60 AND 100 THEN '60 and More'

ELSE 'No Birth Date Entered'

END as AgeRange

from person p, rates tp

where substr(tp.position,1,3) = :grade

and tp.empno = p.empnono

group by date_of_birth, tp.position

order by agerange

I am just a newbie at this so there is probably an easy solution to this problem. Here is my code . I am trying to get the total of males, females and unknowns in each agerange. At the moment for each person i am getting a line i of output

Agerange Male Female Unknown

30-39 1 0 0

30-39 0 1 0

and so on. All i want to get is a total for each age range

Thank you in Advance

select

sum(decode(gender,'M',1,0)) "males",

sum(decode(gender,'F',1,0)) "females",

sum(decode(gender,Null, 1,0)) "Unknown",

date_of_birth, tp.position,

months_between (sysdate, date_of_birth) / 12 as age_in_years,

round (months_between (sysdate, date_of_birth) / 12) as age,

case

when months_between (sysdate, date_of_birth) / 12 BETWEEN 20 AND 29.5 then '20-29'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 30 AND 39.5 THEN '30-39'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 40 AND 49.5 THEN '40-49'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 50 AND 59.5 THEN '50-59'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 60 AND 100 THEN '60 and More'

ELSE 'No Birth Date Entered'

END as AgeRange

from person p, rates tp

where substr(tp.position,1,3) = :grade

and tp.empno = p.empnono

group by date_of_birth, tp.position

order by agerange

## and Tom said...

ops$tkyte%ORA10GR2> create table t as select hiredate date_of_birth, decode( deptno, 10, 'M', 'F' ) gender from scott.emp; Table created. ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> select 2 case 3 when months_between (sysdate, date_of_birth) / 12 BETWEEN 20 AND 29.5 then '20-29' 4 when months_between (sysdate, date_of_birth) / 12 BETWEEN 30 AND 39.5 THEN '30-39' 5 when months_between (sysdate, date_of_birth) / 12 BETWEEN 40 AND 49.5 THEN '40-49' 6 when months_between (sysdate, date_of_birth) / 12 BETWEEN 50 AND 59.5 THEN '50-59' 7 when months_between (sysdate, date_of_birth) / 12 BETWEEN 60 AND 100 THEN '60 and More' 8 ELSE 'No Birth Date Entered' 9 END as AgeRange, 10 count( case when gender = 'M' then 1 end ) male, 11 count( case when gender = 'F' then 1 end ) female 12 from t 13 group by 14 case 15 when months_between (sysdate, date_of_birth) / 12 BETWEEN 20 AND 29.5 then '20-29' 16 when months_between (sysdate, date_of_birth) / 12 BETWEEN 30 AND 39.5 THEN '30-39' 17 when months_between (sysdate, date_of_birth) / 12 BETWEEN 40 AND 49.5 THEN '40-49' 18 when months_between (sysdate, date_of_birth) / 12 BETWEEN 50 AND 59.5 THEN '50-59' 19 when months_between (sysdate, date_of_birth) / 12 BETWEEN 60 AND 100 THEN '60 and More' 20 ELSE 'No Birth Date Entered' 21 END 22 / AGERANGE MALE FEMALE --------------------- ---------- ---------- 20-29 3 9 No Birth Date Entered 0 2 ops$tkyte%ORA10GR2>

## Rating

Is this answer out of date? If it is, please let us know via a Comment

# Comments

select

2 case

3 when months_between (sysdate, date_of_birth) / 12 BETWEEN 20 AND 29.5 then

'20-29'

4 when months_between (sysdate, date_of_birth) / 12 BETWEEN 30 AND 39.5 THEN

'30-39'

5 when months_between (sysdate, date_of_birth) / 12 BETWEEN 40 AND 49.5 THEN

'40-49'

6 when months_between (sysdate, date_of_birth) / 12 BETWEEN 50 AND 59.5 THEN

'50-59'

7 when months_between (sysdate, date_of_birth) / 12 BETWEEN 60 AND 100 THEN

'60 and More'

8 ELSE 'No Birth Date Entered'

9 END as AgeRange,

10 count( case when gender = 'M' then 1 end ) male,

11 count( case when gender = 'F' then 1 end ) female

12 from t

13 group by

14 case

15 when months_between (sysdate, date_of_birth) / 12 BETWEEN 20 AND 29.5 then

'20-29'

16 when months_between (sysdate, date_of_birth) / 12 BETWEEN 30 AND 39.5 THEN

'30-39'

17 when months_between (sysdate, date_of_birth) / 12 BETWEEN 40 AND 49.5 THEN

'40-49'

18 when months_between (sysdate, date_of_birth) / 12 BETWEEN 50 AND 59.5 THEN

'50-59'

19 when months_between (sysdate, date_of_birth) / 12 BETWEEN 60 AND 100 THEN

'60 and More'

20 ELSE 'No Birth Date Entered'

21 END

22 /

2 case

3 when months_between (sysdate, date_of_birth) / 12 BETWEEN 20 AND 29.5 then

'20-29'

4 when months_between (sysdate, date_of_birth) / 12 BETWEEN 30 AND 39.5 THEN

'30-39'

5 when months_between (sysdate, date_of_birth) / 12 BETWEEN 40 AND 49.5 THEN

'40-49'

6 when months_between (sysdate, date_of_birth) / 12 BETWEEN 50 AND 59.5 THEN

'50-59'

7 when months_between (sysdate, date_of_birth) / 12 BETWEEN 60 AND 100 THEN

'60 and More'

8 ELSE 'No Birth Date Entered'

9 END as AgeRange,

10 count( case when gender = 'M' then 1 end ) male,

11 count( case when gender = 'F' then 1 end ) female

**,**

count( case when gender = 'M' then 0

when gender = 'F' then 0

else 1 end) unknowncount( case when gender = 'M' then 0

when gender = 'F' then 0

else 1 end) unknown

12 from t

13 group by

14 case

15 when months_between (sysdate, date_of_birth) / 12 BETWEEN 20 AND 29.5 then

'20-29'

16 when months_between (sysdate, date_of_birth) / 12 BETWEEN 30 AND 39.5 THEN

'30-39'

17 when months_between (sysdate, date_of_birth) / 12 BETWEEN 40 AND 49.5 THEN

'40-49'

18 when months_between (sysdate, date_of_birth) / 12 BETWEEN 50 AND 59.5 THEN

'50-59'

19 when months_between (sysdate, date_of_birth) / 12 BETWEEN 60 AND 100 THEN

'60 and More'

20 ELSE 'No Birth Date Entered'

21 END

22 /

count( case when gender = 'M' then 0

when gender = 'F' then 0

else 1 end) unknown

this can be get a wrong result.must be

sum( case when gender = 'M' then 0

when gender = 'F' then 0

else 1 end) unknown

when gender = 'F' then 0

else 1 end) unknown

this can be get a wrong result.must be

sum( case when gender = 'M' then 0

when gender = 'F' then 0

else 1 end) unknown

COUNT can be used in the following ways;

count( case when gender NOT IN ( 'M', 'F') then 1 end)

OR

count( case when gender = 'M' then Null

when gender = 'F' then Null

else 1 end) unknown

The above CASE statement will result 1 if the gender is neither M nor F and will result Null otherwise and the COUNT will not include the Nulls; so we get the desired output.

count( case when gender NOT IN ( 'M', 'F') then 1 end)

OR

count( case when gender = 'M' then Null

when gender = 'F' then Null

else 1 end) unknown

The above CASE statement will result 1 if the gender is neither M nor F and will result Null otherwise and the COUNT will not include the Nulls; so we get the desired output.