# count on age

### Question

• hi

i have 1 field on my table _ date of birth

what i need is to find age and then category no. of people between that age group

exmaple: age 5 - 10 : 20

age 15-20 :  50 people

how to calculate that?

Wednesday, April 03, 2013 8:16 PM

• Hi,

Here's an example for two groups (I treated age as a number for simplicity. You might need to change and work with dates):

SELECT
SUM(CASE WHEN Age BETWEEN 5 AND 10 THEN 1 ELSE 0) AS FiveToTen,
SUM(CASE WHEN Age BETWEEN 15 AND 20 THEN 1 ELSE 0) AS FifteenTo20
FROM YourTable

My Blog

• Marked as answer by Thursday, April 04, 2013 4:16 PM
Wednesday, April 03, 2013 9:56 PM
• First, you may have to calculate Age from DOB and then use Matan Yungman's example -

Example

GO
/****** Object:  UserDefinedFunction [dbo].[ufnGetAgeFromDOB] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION  [dbo].[ufnGetAgeFromDOB]
(
@DateOfBirth1 DATETIME,@MeasureDate1 DATETIME
)
RETURNS INT
AS
BEGIN

DECLARE @DateOfBirth DATETIME=@DateOfBirth1
,@MeasureDate DATETIME=@MeasureDate1
DECLARE @Age INT
DECLARE @Yr1 INT
DECLARE @Yr2 INT

SET @Yr1 = YEAR(@DateOfBirth)
SET @Yr2 = YEAR(@MeasureDate)

SET @Age = @Yr2 - @Yr1

IF MONTH(@MeasureDate) < MONTH(@DateOfBirth)
BEGIN
SET @Age = @Age - 1
END

IF MONTH(@MeasureDate) = MONTH(@DateOfBirth)
BEGIN
IF DAY(@MeasureDate) < DAY(@DateOfBirth)
BEGIN
SET @Age = @Age - 1
END
END

RETURN @Age

END

Then,

;WITH cte AS
(
,JobTitle
,BirthDate
,dbo.ufnGetAgeFromDOB(BirthDate,GETDATE()) AS Age
)
SELECT
SUM(CASE WHEN Age BETWEEN 50 AND 70 THEN 1 ELSE 0 END) AS [Age50To100],
SUM(CASE WHEN Age BETWEEN 20 AND 49 THEN 1 ELSE 0 END) AS [Age20To49]
FROM cte

Narsimha

• Marked as answer by Thursday, April 04, 2013 4:16 PM
Wednesday, April 03, 2013 10:55 PM
• Do you mean this ?

SELECT [15-20]
,[20-25]
,[25-30]
,..
,..
,..
INTO temp1
FROM cte_countdistinct

SELECT *
FROM temp1

Narsimha

• Marked as answer by Thursday, April 04, 2013 4:16 PM
Thursday, April 04, 2013 2:10 PM

### All replies

• Hi,

Here's an example for two groups (I treated age as a number for simplicity. You might need to change and work with dates):

SELECT
SUM(CASE WHEN Age BETWEEN 5 AND 10 THEN 1 ELSE 0) AS FiveToTen,
SUM(CASE WHEN Age BETWEEN 15 AND 20 THEN 1 ELSE 0) AS FifteenTo20
FROM YourTable

My Blog

• Marked as answer by Thursday, April 04, 2013 4:16 PM
Wednesday, April 03, 2013 9:56 PM
• First, you may have to calculate Age from DOB and then use Matan Yungman's example -

Example

GO
/****** Object:  UserDefinedFunction [dbo].[ufnGetAgeFromDOB] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION  [dbo].[ufnGetAgeFromDOB]
(
@DateOfBirth1 DATETIME,@MeasureDate1 DATETIME
)
RETURNS INT
AS
BEGIN

DECLARE @DateOfBirth DATETIME=@DateOfBirth1
,@MeasureDate DATETIME=@MeasureDate1
DECLARE @Age INT
DECLARE @Yr1 INT
DECLARE @Yr2 INT

SET @Yr1 = YEAR(@DateOfBirth)
SET @Yr2 = YEAR(@MeasureDate)

SET @Age = @Yr2 - @Yr1

IF MONTH(@MeasureDate) < MONTH(@DateOfBirth)
BEGIN
SET @Age = @Age - 1
END

IF MONTH(@MeasureDate) = MONTH(@DateOfBirth)
BEGIN
IF DAY(@MeasureDate) < DAY(@DateOfBirth)
BEGIN
SET @Age = @Age - 1
END
END

RETURN @Age

END

Then,

;WITH cte AS
(
,JobTitle
,BirthDate
,dbo.ufnGetAgeFromDOB(BirthDate,GETDATE()) AS Age
)
SELECT
SUM(CASE WHEN Age BETWEEN 50 AND 70 THEN 1 ELSE 0 END) AS [Age50To100],
SUM(CASE WHEN Age BETWEEN 20 AND 49 THEN 1 ELSE 0 END) AS [Age20To49]
FROM cte

Narsimha

• Marked as answer by Thursday, April 04, 2013 4:16 PM
Wednesday, April 03, 2013 10:55 PM
• cte_countdistinct("15-20","20-25","25-30","30-35","35-40","40-45","45-50","50-55","55-60","60-65","65-70","70-75","75-80","80-85","85-90")
as
(select

sum(case when AgeYearsIntRound between 15 and 20 then countprescription else 0 end)as "15-20",

sum(case when AgeYearsIntRound between 20 and 25 then countprescription else 0 end)as "20-25",

sum(case when AgeYearsIntRound between 25 and 30 then countprescription else 0 end)as "25-30",

sum(case when AgeYearsIntRound between 30 and 35 then countprescription else 0 end)as "30-35",

sum(case when AgeYearsIntRound between 35 and 40 then countprescription else 0 end)as "35-40",

sum(case when AgeYearsIntRound between 40 and 45 then countprescription else 0 end)as "40-45",

sum(case when AgeYearsIntRound between 45 and 50 then countprescription else 0 end)as "45-50",

sum(case when AgeYearsIntRound between 50 and 55 then countprescription else 0 end)as "50-55",

sum(case when AgeYearsIntRound between 55 and 60 then countprescription else 0 end)as "55-60",

sum(case when AgeYearsIntRound between 60 and 65 then countprescription else 0 end)as "60-65",

sum(case when AgeYearsIntRound between 65 and 70 then countprescription else 0 end)as "65-70",

sum(case when AgeYearsIntRound between 70 and 75 then countprescription else 0 end)as "70-75",

sum(case when AgeYearsIntRound between 75 and 80 then countprescription else 0 end)as "75-80",

sum(case when AgeYearsIntRound between 80 and 85 then countprescription else 0 end)as "80-85",

sum(case when AgeYearsIntRound between 85 and 90 then countprescription else 0 end)as "85-90"

from cte_CountAge)

now what i want is i need all this values in table

table:

age count

15-20       342

20-25          345

hwo to do that from cte or any other way

Thursday, April 04, 2013 1:57 PM
• Do you mean this ?

SELECT [15-20]
,[20-25]
,[25-30]
,..
,..
,..
INTO temp1
FROM cte_countdistinct

SELECT *
FROM temp1

Narsimha

• Marked as answer by Thursday, April 04, 2013 4:16 PM
Thursday, April 04, 2013 2:10 PM
• ya

but my table has two column ,

age count

from cte_countdistinct i am getting count ,

i  need to insert age manually

like

select

15-20(age)   234(count)

into temp1

hwo to do that

Thursday, April 04, 2013 2:14 PM
• A case statement will work well for small scale applications.  But if you have to do this a lot I recommend going ahead and creating a date range table with at least three fields - a lower, upper, and description.

CREATE TABLE AgeRanges
ID int Identity NOT NULL,
Lower int NULL,
Upper int NULL,
AgeRange varchar(25) NULL

Populate it with your values - I went ahead and filled in the gaps in your ranges

Insert into AgeRanges (lower, upper, AgeRange)
Values
(0,4, '0-4'),
(5,10,'5-10'),
(11,14,'11-14'),
(15,20, '15-20'),
(21,999, '21 to death')

Lets assume the existence of an Individuals table with the fields IdNo, FName, LName, and BirthDate.  From that the you can calculate the age and place it in the correct range with:

Select
IdNo
, FName
, LName
, DATEDIFF(Hour,BirthDate, GETDATE())/8766 As Age
, AgeRange
From
Individuals I
Left Join AgeRanges AR on ((DATEDIFF(Hour,BirthDate, GETDATE())/8766) Between Lower and Upper)

Note that you're almost certain to have bad birth dates and you  have to decide how to handle them.  I've used this approach because I don't have to maintain a lot of case statements and when the age ranges change it's a simple step to modify the data.

Good luck

Tim Mills-Groninger

Thursday, April 04, 2013 2:24 PM