Query to return gender count per age group

[IoMatua]

Here's the situtaion.

I have a table (called Main)which holds beneficiary information (Name, Address, Date of Birth .. etc.). What I want to do is write a query (or set of queries) that returns a gender count per age "cohort" using only information stored in the Main table.

The cohorts are - 00-04, 05-09, 10-14, ..., etc. up to the final cohort, 65+.

I'm wanting to get a result that has each column mapped to each of the cohorts, and 2 rows, one for Male count and one for Female count and each row will have a count of the number of Male/Females in that particular age cohort.

I hope this is enough info.
ANY suggestions will be greatly appreaciated.
Thanks.

 

[tlbroadbent]

You could write a query like the following.

Select Gender,
Sum(IIF(Grp=0,1,0)) As [00-04],
Sum(IIF(Grp=1,1,0)) As [05-09],
Sum(IIF(Grp=2,1,0)) As [10-14],
... the rest of the age groups ...
Sum(IIF(Grp=12,1,0)) As [60-64],
Sum(IIF(Grp>12,1,0)) As [65+]
From (Select
Gender, Fix(datediff("d", [DOB], date())/365.25/5) As Grp
From Main) As GrpQry
Group By Gender; If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm