Hello everyone,
I'm trying to get a count of 3 different types on the same field. For Example, let's use Gender as the field with these options: Male, Female, Not Given. What I'm wanting to do is retrieve a count for each type. What I have so far is: SELECT COUNT(Gender) WHERE Gender = 'Male' and I have to duplicate this in 3 different data controls. I would like, however, to have one datasource control with a statement along the lines of:
SELECT ( SELECT COUNT(Gender) FROM Users WHERE Gender='Male), SELECT COUNT(Gender) FROM Users WHERE Gender='Female', SELECT COUNT(Gender) WHERE Gender='NotGiven' )
From Users
Or something to that effect. Any suggestions?
Thank you greatly for your help,
Mark
SELECTSUM(CASEWHEN Gender='Male'THEN 1ELSE 0END)as MaleCount,
SUM(CASEWHEN Gender='Female'THEN 1ELSE 0END)as FemaleCount,
SUM(CASEWHEN Gender='NotGiven'Or GenderISNULLTHEN 1ELSE 0END)as NotGivenCount
FROM Users
|||This ended up working for me: SELECT Count_1 = (SELECT COUNT(Gender) FROM User WHERE Gender='male'), Count_2 = (SELECT COUNT(Gender) FROM Users WHERE Gender='female') and then access Count_1 and Count_2
Thanks for your help.. I will mark yours as the answer because it looks like it would work too.
