• Questions
• # Mutliple Rows using (Case Statement)

Thanks for the question.

Asked: February 09, 2007 - 9:48 am UTC

Last updated: February 09, 2007 - 9:52 am UTC

Version: 8.0.6

Viewed 1000+ times

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

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
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

Peter G. Evans, February 09, 2007 - 2:39 pm UTC

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,
count( 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 /

### review

honghong, February 10, 2007 - 8:12 pm UTC

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

### just another way to use COUNT

Wasif Hassan, February 12, 2007 - 2:45 am UTC

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.