Hello,

I have a column named Gender in my table. Its a varchar2 type column
and each row is either 'M' or and 'F'. I want to count the no. of Ms
and number of Fs and obtain the result of the number of Ms and Fs in a
separate row.

Right now I am only able to perform the count function and obtain the
number of Ms and Fs. But I am not able to get the total for Ms and Fs.
So can some one please tell me how should I do it?

Right now I am giving the following command:

SELECT GENDER, COUNT(GENDER) As American_Indian
FROM STUDENT_DETAIL
WHERE ETHNIC_GROUP_CODE = 1
GROUP BY GENDER ORDER BY GENDER DESC

And the output is coming as

Gender American_Indian
-----------------------------------------
M 60
F 60

What I want to see is

Gender American_Indian
-----------------------------------------
M 60
F 60
MF 120

Please help.

Thank you.

Krishna.

Hi:
you can use an union:

SELECT GENDER, COUNT(GENDER) As American_Indian
FROM STUDENT_DETAIL
WHERE ETHNIC_GROUP_CODE = 1
GROUP BY GENDER
union
SELECT 'MF',COUNT(*)
FROM STUDENT_DETAIL
WHERE ETHNIC_GROUP_CODE = 1 AND GENDER IN ('M','F')
ORDER BY GENDER DESC

I hope it's useful to you.
Regards,
Isa

Thanks a lot Isa,

That really helps. Have a great day.

Regards,

Krishna.

Hi,

.... a bit shorter version

SELECT COUNT(gender), gender, NVL(gender, 'ALL') FROM test_gender
GROUP BY GROUPING SETS(gender,0)

Regards
Adrian

....correction
SELECT COUNT(gender), NVL(gender, 'ALL') a FROM Student_detail
WHERE ethnic_group_code = 1
GROUP BY GROUPING SETS(gender,0) ORDER BY a DESC

Best regards,
Adrian

Hi,

Thanks a lot all for your help. Now I need to interchange the output
slightly.
Instead of this

Gender American_Indian
-----------------------------------------
M 60
F 60
MF 120

I want the output as
Race Male Female
-------------------------------------
Race 1 60 60
Race 2 70 50

I am getting it separately for each column but I am not able to show
Male and Female as 2 separate columns.

I've tried the following:


SELECT DISTINCT
DECODE(GENDER, 'M',
COUNT(GENDER),NULL) MALE
FROM STUD_DTL
GROUP BY GENDER;

Not executing giving errors


SELECT COUNT(GENDER) MALE and COUNT(GENDER) FEMALE
FROM STUD_DTL WHERE GENDER = 'M' AND GENDER = 'F'
ORDER BY GENDER;

SELECT COUNT(GENDER) MALE, COUNT(GENDER) FEMALE
FROM STUD_DTL WHERE GENDER = 'M' AND GENDER = 'F'
ORDER BY GENDER;

None of the above commands is working. Can you please help me?

Thank you.

Krish.

"Krishna" <mpkrishna25@yahoo.com> a écrit dans le message de news: 1140044911.332349.77810@g44g2000cwa.googlegroups.c om...
| Hi,
|
| Thanks a lot all for your help. Now I need to interchange the output
| slightly.
| Instead of this
|
| Gender American_Indian
| -----------------------------------------
| M 60
| F 60
| MF 120
|
| I want the output as
| Race Male Female
| -------------------------------------
| Race 1 60 60
| Race 2 70 50
|
| I am getting it separately for each column but I am not able to show
| Male and Female as 2 separate columns.
|
| I've tried the following:
|
|
| SELECT DISTINCT
| DECODE(GENDER, 'M',
| COUNT(GENDER),NULL) MALE
| FROM STUD_DTL
| GROUP BY GENDER;
|
| Not executing giving errors
|
|
| SELECT COUNT(GENDER) MALE and COUNT(GENDER) FEMALE
| FROM STUD_DTL WHERE GENDER = 'M' AND GENDER = 'F'
| ORDER BY GENDER;
|
| SELECT COUNT(GENDER) MALE, COUNT(GENDER) FEMALE
| FROM STUD_DTL WHERE GENDER = 'M' AND GENDER = 'F'
| ORDER BY GENDER;
|
| None of the above commands is working. Can you please help me?
|
| Thank you.
|
| Krish.
|

select sum(decode(gender,'M',1,0)) male,
sum(decode(gender,'F',1,0)) female,
sum(decode(gender,'M',0,'F',0,1)) other,
count(*) total
from stud_dtl
/

Regards
Michel Cadot

In article <1139846992.606755.128210@g47g2000cwa.googlegroups .com>,
mpkrishna25@yahoo.com says...
> Hello,
>
> I have a column named Gender in my table. Its a varchar2 type column
> and each row is either 'M' or and 'F'. I want to count the no. of Ms
> and number of Fs and obtain the result of the number of Ms and Fs in a
> separate row.
>
> Right now I am only able to perform the count function and obtain the
> number of Ms and Fs. But I am not able to get the total for Ms and Fs.
> So can some one please tell me how should I do it?
>
> Right now I am giving the following command:
>
> SELECT GENDER, COUNT(GENDER) As American_Indian
> FROM STUDENT_DETAIL
> WHERE ETHNIC_GROUP_CODE = 1
> GROUP BY GENDER ORDER BY GENDER DESC
>
> And the output is coming as
>
> Gender American_Indian
> -----------------------------------------
> M 60
> F 60
>
> What I want to see is
>
> Gender American_Indian
> -----------------------------------------
> M 60
> F 60
> MF 120
>
> Please help.
>

select GENDER, COUNT(GENDER)
from...
group by rollup(gender)

Regards,
Jan