Count Occurances Of Given Value

Hello All,

I have a question that has been vexing me for some time now. It keeps coming up when I'm trying to write queries for SSRS reports. Lets say I have a table that has 3 columns to keep track of people's gender in a annonomys survay (very basic example):

Month (varchar) | Year (smallint) | Gender (bool)

I want to return a dataset that is grouped by Month and Year and that contains a count of each Gender which would look something like this:

Month | Year | [Male Count] | [Female Count]

January | 2006 | 100 | 120
February | 2006 | 130 | 110
March | 2006 | 120 | 145
April | 2006 | 105 | 125

How would I acheive a dataset like this? Is it possible? Do I need to join the table to itself? If so do I use an Inner Join, an Outer Join, or a Left/Right Join? Any help would be extremely appreciated.

Thanks!

Tennyson

There are several possibilities. One option:

SELECT [Month], [Year],
COUNT(CASE WHEN Gender = 0 THEN 1 END) AS MaleCount,
COUNT(CASE WHEN Gender = 1 THEN 1 END) AS FemaleCount
FROM YourSurveyTable

GROUP BY [Month], [Year]

-Sue

|||

Thank you Sue! Your help was much appreciated.

Thanks,

Tennyson