MS SQL Get Count of Records in Age Range (by year of birth)

I have a users table that has one record per user. It includes (among other things) their user ID (int) and date of birth (datetime)

I want to query and get a COUNT of users in each of the following age ranges:
6 or younger
7-9 years old
10-13 years old
14-17 years old
18-20 years old
21-25 years old
26-30 years old
31-40 years old
41-50 years old
50+ years old

(As you can see, the date ranges aren't even intervals)

Using the SQL below, I can get the count of users by birth year/age -- but how can I get the count in those age RANGES?? (So I can bind it to a gridview or something similar to display.)

SELECT Count(DATEPART(yyyy,DOB)) As 'UserCount'
	, DATEPART(yyyy,DOB) As 'Year'
	, (DATEPART(yyyy,GETDATE()) - DATEPART(yyyy,DOB)) As 'Age'
FROM [User] u
WHERE DATEPART(yyyy,DOB) IS NOT NULL
GROUP BY DATEPART(yyyy,DOB)
ORDER BY DATEPART(yyyy,DOB) DESC

Open in new window


This is sample output from the query above -- which shows per year. I need per age range as grouped how I explained above.

UserCount / Year / Age
2      2000      10
3      1999      11
10      1998      12
14      1997      13
12      1996      14
3      1995      15
5      1994      16
5      1993      17
1      1992      18
4      1990      20
1      1989      21
1      1986      24
1      1985      25
2      1984      26
2      1983      27
6      1982      28

ETC...
mandi224Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
QlemoConnect With a Mentor DeveloperCommented:
Remember: You can't just subtract the years to get the age, the age always depends on whether the birthday has passed already in this year.

select result.class, count(*)
from 
(
select
  case when age <= 6 then '6 or younger'
           when age between 7 and 9 then '7-9'
           when age between 10 and 13 then '10-13'
           when age between 14 and 17 then '14-17'
/* and so on */
  end class
from (select datepart(yyyy, getdate()-DOB) as age from user where DOB is not null) age
) result
group by result.class

Open in new window

0
 
vinodchCommented:
Below is the code, first creating a temp table to get age
Select
*
From
(
SELECT Count(DATEPART(yyyy,DOB)) As 'UserCount'
      , DATEPART(yyyy,DOB) As 'Year'
      , (DATEPART(yyyy,GETDATE()) - DATEPART(yyyy,DOB)) As 'Age'
FROM [User] u
GROUP BY DATEPART(yyyy,DOB)
)A
WHERE Age Between @minAge and @MaxAge

0
 
carsRSTCommented:
I'm not in a position to test but would try something like this...(just fill in ranges)


 SELECT Count(DATEPART(yyyy,DOB)) As 'UserCount'
      , DATEPART(yyyy,DOB) As 'Year'
      , (DATEPART(yyyy,GETDATE()) - DATEPART(yyyy,DOB))
      case when (DATEPART(yyyy,GETDATE()) - DATEPART(yyyy,DOB)) between 7 and 9 then '7-9 years old'
      when (DATEPART(yyyy,GETDATE()) - DATEPART(yyyy,DOB)) between 10 and 13 then '10-13 years old'
      end
      As 'Age'
FROM [User] u
WHERE DATEPART(yyyy,DOB) IS NOT NULL
GROUP BY DATEPART(yyyy,DOB)
ORDER BY DATEPART(yyyy,DOB) DESC
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mandi224Author Commented:
I just had to modify the select that was inside the "FROM" to be
SELECT DATEPART(yyyy,GETDATE()) - DATEPART(yyyy,DOB) As 'Age'
            FROM [User] WHERE DOB IS NOT NULL

THANKS!
0
 
mandi224Author Commented:
WHOOPS - Qlemo, I see what you mean now about the "age depends on whether the birthday has passed already"....
My version doesn't take that into account. But your version (where the "FROM" has "select datepart(yyyy, getdate()-DOB) as age from user where DOB is not null" gives me years, not ages to select from, so it doesn't work...
0
 
mandi224Author Commented:
OK I got it now ...


select result.class As 'AgeRange', count(*) As 'UserCount'
from 
(
	select
		case when age <= 6 then '6 or younger'
			when age between 7 and 9 then '7-9'
			when age between 10 and 13 then '10-13'
			when age between 14 and 17 then '14-17'
			when age between 18 and 20 then '18-20'
			when age between 21 and 25 then '21-25'
			when age between 26 and 30 then '26-30'
			when age between 31 and 40 then '31-40'
			when age between 41 and 50 then '41-50'
			when age between 51 and 100 then '51+'
	end class
	from (select DATEDIFF(year,DOB,GETDATE()) As age from [user] where DOB is not null) age
) result
group by result.class

Open in new window

0
 
QlemoDeveloperCommented:
Ahh, I see. Sorry for that.
But your last case isn't correct - there might be people older than 100 years ;-).
   when age between 51 and 100 then '51+'
should be
   when age >= 51then '51+'
0
 
mandi224Author Commented:
Qlemo - good point, thank you. Gotta account for those 101-year olds! ;)

Thanks for all your help!
0
All Courses

From novice to tech pro — start learning today.