?
Solved

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

Posted on 2010-10-05
8
Medium Priority
?
1,912 Views
Last Modified: 2012-05-10
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...
0
Comment
Question by:mandi224
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 4

Expert Comment

by:vinodch
ID: 33832488
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
 
LVL 16

Expert Comment

by:carsRST
ID: 33832533
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
 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 33832602
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Closing Comment

by:mandi224
ID: 33832750
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
 

Author Comment

by:mandi224
ID: 33833056
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
 

Author Comment

by:mandi224
ID: 33833247
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 33833291
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
 

Author Comment

by:mandi224
ID: 33833336
Qlemo - good point, thank you. Gotta account for those 101-year olds! ;)

Thanks for all your help!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question