Couting the number of Males and Females in a databse

[Mayoor]

Hi I have a databse which consists of males and females. The field which records this is called "gender" I need an SQL statement which will count the number of males and count the number of males and females and return the respective counts to my ASP page.

I'd like to preferably have the counts as a recordset if possible, in this format

Males Females
456 3000


Can Anyone help.

Cheers

Mayoor

 

[SQLBill]

Maybe this will work...(disclaimer, I haven't tested it)..

SELECT SUM(CASE WHERE Gender = 'Male' THEN 1 ELSE 0) AS 'Male',
SUM(CASE WHERE Gender = 'Female' THEN 1 ELSE 0) AS 'Female'
FROM Table_name

-SQLBill

 

[jby1]

select gender, count(gender)
from table
group by gender

 

[Mayoor]

SQL Bill - that statement wont work in Query Analyser

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'where'.

 

[nigelrivett]

swap where for when and add an end.

SELECT SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) AS 'Male',
SUM(CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END) AS 'Female'
FROM Table_name

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBill]

Thanks Nigel....I must have been daydreaming when I used those WHEREs instead of WHENs.

-SQLBill