Please sugges me a query for the proble

[kishorecvr]

I have a table like

SEX NAME
----- --------
M KISHORE
M KUMAR
M DILIP
M CVR
M CKR
F REKHA
F RANI
F PANDU

and I want to retrieve data like

SEX TOTAL MALE/FEMALE PERCENTAGE
------ ---------------- ----------
M 5 62.5
F 3 37.5

Can any one suggest me a solution?

Thanks in advance.

Kishore.

 

[carp]

This is one of those that will probably vary by RDBMS.
Here's one way to do it in Oracle:

SELECT m.males,
m.males/(m.males + f.females) m_pct,
f.females,
f.females/(m.males + f.females) f_pct
FROM
(SELECT count(*) males
FROM my_table
WHERE sex = 'M') m,
(SELECT COUNT(*) females
FROM my_table
WHERE sex = 'F') f;

 

[fluteplr]

This should work on most sql databases

select sex, count(*) total,
count(*) / (select count(*) from mytable) percentage
from mytable
group by sex order by sex