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
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
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment