Create range bins from SQL Server table for histograms

Question

I have the following table in SQL Server:

----------------------------- ID       Age          Gender 1          30           F 2          35           M 3          32           M 4          18           F 5          21           F 

What I need to do is to execute a query that will group the records in given ranges and count the occurences. The results need to be displayed later in a histogram chart (bar chart). I tried a query similar to the following:

SELECT  count(CASE WHEN Age>= 10 AND Age < 20 THEN 1 END) AS '10 - 20', count(CASE WHEN Age>= 21 AND Age < 30 THEN 1 END) AS '21 - 30', count(CASE WHEN Age>= 31 AND Age < 35 THEN 1 END) AS '31 - 35', count(CASE WHEN Age>= 36 AND Age < 40 THEN 1 END) AS '36 - 40', FROM (SELECT Age FROM Attendees) AS AgeGroups 

For the moment, this does the trick, but does not consider the gender column. It will result a single row which counts the frequency of every age group:

10-20     21-30     31-35     36-40   0         22        21        13 

If gender would be considered there should be two records shown, for each gender. I need to see something like:

Gender    10-20     21-30     31-35     36-40   M         0         12        9         6   F         0         10        12        7 

How should I approach this problem?

Answer 1

Simply add the Gender column to your SELECT and then do a GROUP BY.

SELECT  Gender, count(CASE WHEN Age>= 10 AND Age < 20 THEN 1 END) AS [10 - 20], count(CASE WHEN Age>= 21 AND Age < 30 THEN 1 END) AS [21 - 30], count(CASE WHEN Age>= 31 AND Age < 35 THEN 1 END) AS [31 - 35], count(CASE WHEN Age>= 36 AND Age < 40 THEN 1 END) AS [36 - 40] FROM Attendees AS AgeGroups GROUP BY Gender