none
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

Answers

  • 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 tsql_new 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

    USE [AdventureWorks]
    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 
    (
    SELECT BusinessEntityId
    	,JobTitle
    	,BirthDate
    	,dbo.ufnGetAgeFromDOB(BirthDate,GETDATE()) AS Age
    FROM AdventureWorks.HumanResources.Employee
    )
    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 tsql_new 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 tsql_new 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 tsql_new 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

    USE [AdventureWorks]
    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 
    (
    SELECT BusinessEntityId
    	,JobTitle
    	,BirthDate
    	,dbo.ufnGetAgeFromDOB(BirthDate,GETDATE()) AS Age
    FROM AdventureWorks.HumanResources.Employee
    )
    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 tsql_new 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 tsql_new 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