Converting query from Access

Hi,

I would like some help converting an access query to a SQL Server query.

The access query is made up of the following and then repeated for each field:
SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...
FROM dbo.applicants
I have tried using the following to test out an alternative, but it brings back the incorrect figure:
SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS Female
FROM dbo.applicants
I've looked at the table and should get back 350, but only get back 193.
But using the following query I get the correct figure:
SELECT COUNT(gender) AS Female
FROM applicants
GROUP BY gender
HAVING (gender = 'Female')
Although I can't use the above query because I want to also count how many 'Male' applicants there are.

How can I do this?

ThanksAre you sure you don't get 350 and should get 193? Anyhoo - how come you changed SUM to COUNT? ;)|||I'm actually doing this in Visual Studio, I've created a dataset to select all records but it only returns 193 instead of 557 rows. :S|||the expression with SUM is...

SELECT SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS Female

if you want to use COUNT then the expression should be...

SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE NULL END) AS Female

note that you could also use ELSE NULL with SUM :)|||Doesn't matter, found out I was using the wrong SQL Server. :o

Although while I have opened the thread, is it possible to work out a percentage within the SQL Query.
What I have been doing is summing up each field to get a figure, then in my application dividing it to get the percentage.
Is there a better way to do this within the query?|||Hide behind that rock ;)