Counting Age Groups

Question

• I have a table of Individuals that I would like to have a total count of age ranges in separate columns. The conditional counts I am attempting has syntax errors I can't figure out, or maybe the function in my expression is inappropriate.

SELECT COUNT((Case WHEN dbo.ufn_GETAGE(Birthdate, GETDATE()) BETWEEN 4 AND 8 THEN Birthdate)) AS [8 & Under], COUNT((Case WHEN dbo.ufn_GETAGE(Birthdate, GETDATE()) BETWEEN 9 AND 10 THEN Birthdate)) AS [10 & Under], COUNT((Case WHEN dbo.ufn_GETAGE(Birthdate, GETDATE()) BETWEEN 11 AND 14 THEN Birthdate)) AS [14 & Under], COUNT((Case WHEN dbo.ufn_GETAGE(Birthdate, GETDATE()) BETWEEN 15 AND 18 THEN Birthdate)) AS [18 & Under]

FROM Individuals

• Edited by Friday, April 05, 2013 4:50 PM
Friday, April 05, 2013 4:48 PM

• Case ... end

Serg

• Proposed as answer by Friday, April 05, 2013 5:00 PM
• Marked as answer by Friday, April 05, 2013 5:24 PM
Friday, April 05, 2013 4:57 PM

All replies

• Case ... end

Serg

• Proposed as answer by Friday, April 05, 2013 5:00 PM
• Marked as answer by Friday, April 05, 2013 5:24 PM
Friday, April 05, 2013 4:57 PM
• btw, your column names are indicating wrong ranges. 18 and younger indclude 0 to 18, not only 15 to 18. Why are you using an UDF instead of DATEDIFF() directly?

Friday, April 05, 2013 5:19 PM
• That was it!

Thank you.

Friday, April 05, 2013 5:24 PM
• btw, your column names are indicating wrong ranges. 18 and younger indclude 0 to 18, not only 15 to 18. Why are you using an UDF instead of DATEDIFF() directly?

You are right logically. But this list is for a junior team tennis report that uses this age range. The Column names are more titles than descriptions. That is the way the organization named them.
• Edited by Friday, April 05, 2013 5:31 PM Didn't complete the answer
Friday, April 05, 2013 5:30 PM
• btw, your column names are indicating wrong ranges. 18 and younger indclude 0 to 18, not only 15 to 18. Why are you using an UDF instead of DATEDIFF() directly?

You are right logically. But this list is for a junior team tennis report that uses this age range. The Column names are more titles than descriptions. That is the way the organization named them.
Also, I am using the function because it gives me a good age to work with as a number.
Friday, April 05, 2013 5:32 PM