Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38

    Unanswered: To group by range of age

    Hi guys,

    I have these 3 fields :-
    1. Name (varchar)
    2. Sex (varchar)
    3. DateBirth (datetime)

    I want to display data which group by count for Male n Female and devide to 3 range group.
    1. 18-21
    2. 25-30
    3. 35-40

    the date different will be count by field datebirth, with year and getdate().
    Can anyone help me on this. thx

    Regards,
    Shaffiq

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select age_group
         , count(*) as rows
      from (
           select case when datediff(year,DateBirth,getdate()) 
                              between 18 and 21
                       then '18-21'
                       when datediff(year,DateBirth,getdate()) 
                              between 25 and 30
                       then '25-30'
                       when datediff(year,DateBirth,getdate()) 
                              between 35 and 40
                       then '35-40'     
                       else null end   as age_group  
             from daTable
           ) as T
     where age_group is not null
    group
        by age_group
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Rudy

    Is
    Code:
    datediff(year,DateBirth,getdate())
    totally accurate for age?

    Actually - that's just being cheeky - I do believe that it isn't Though I suspect you might tell me that it is And then I'd refer you to something like this http://p2p.wrox.com/topic.asp?TOPIC_ID=6834 And you might point out something that I overlooked (e.g. doesn't apply to bands)

    Anyway - this is the (old, old and really looks like it needs reviewing) code I adapted from the web a while ago for age bands:
    Code:
    CREATE function dbo.AgeBand
     (@datDOB AS datetime,@datOn as datetime)
     returns varchar(12)
    as
    
        begin
        DECLARE @age int, @AgeBand varChar(12)
        IF cast(datepart(m,@datOn) as int) > cast(datepart(m,@datDOB) as int)
         SET @age = cast(datediff(yyyy,@datDOB,@datOn) as int)
        else
         IF cast(datepart(m,@datOn) as int) = cast(datepart(m,@datDOB) as int)
          IF datepart(d,@datOn) >= datepart(d,@datDOB)
           SET @age = cast(datediff(yyyy,@datDOB,@datOn) as int)
          ELSE
           SET @age = cast(datediff(yyyy,@datDOB,@datOn) as int) -1
         ELSE
          SET @age = cast(datediff(yyyy,@datDOB,@datOn) as int) - 1
     if @age = 0
      SET @AgeBand = 'Undetermined'
     if @age is null
      SET @AgeBand = 'Undetermined'
     else
      if @age <=15
       set @ageband = '<=15'
     else
      if @age <=18 
       set @ageband = '16-18'
     else
      if @age >=19
       set @ageband = '19 Plus'
        RETURN @ageband
    end
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Variation of Rudy's that corrects for datediff issues:
    Code:
    select	Sex,
    	age_group,
    	count(*) as rows
    from	(select	Sex,
    		case	when datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), DateBirth) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) between 18 and 21 then '18-21'
    			when datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), DateBirth) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) between 25 and 30 then '25-30'
    			when datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), DateBirth) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) between 35 and 40 then '35-40'     
    			else null end as age_group  
             from daTable) as T
    group by Sex,
    	age_group
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a work of art, blindman

    congrats for spotting the need to include sex, which i overlooked (dunno how)

    however, you omitted the WHERE age_group IS NULL

    your query will return additional rows for people not in the three age bands
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I ommitted the AGEGROUP is null on purpose. I figured he could add that in if he wanted.

    Thanks for the kudos. I seem to do better with addition and subtraction than with division, eh?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    yahuu...

    thx rudy and blindman.... really help me a lot....
    have to xplore more on the sql statement.. a million of thx...

    Regards,
    shaffiq

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by man_at_work22
    .. a million of thx...
    What's the U.S. exchange rate on that? Probably something like the old Italian Lira...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    It seems there is a bug in blindman's calculation due to leap years e.g.
    Code:
    declare @startdate datetime, @enddate datetime
    select @startdate='3 Mar 2000', @enddate='3 Mar 2004'
    select 'Years'=datediff(year, dateadd(day, -datepart(dayofyear, @enddate), @startdate) 
                                , dateadd(day, -datepart(dayofyear, @enddate), @enddate))
    
    Years       
    ----------- 
    4
    
    (1 row(s) affected)
    
    select @startdate='3 Mar 2000', @enddate='3 Mar 2005'
    select 'Years'=datediff(year, dateadd(day, -datepart(dayofyear, @enddate), @startdate) 
                                , dateadd(day, -datepart(dayofyear, @enddate), @enddate))
    
    Years       
    ----------- 
    4
    
    (1 row(s) affected)

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Although untidy I've been using the function I posted for several years now. It does correct for leap years (just used the Age function (without bands) it is based on and it correctly returns 4 and 5).

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    In this code I compare char values, the leading 0 will allow the same result as a numeric compare.
    Code:
    select sex
    ,'agegroup'=case 
     when age between 18 and 21 then '18-21'
     when age between 25 and 30 then '25-30'
     when age between 35 and 40 then '35-40'
     end
    ,count(*)
    from  (select sex
           ,'age'=datepart(yy,getdate())-datepart(yy,DateBirth)
           - case when convert(char(8),getdate(),10) < convert(char(8),DateBirth,10)
                  then 1 else 0 end
           from daTable) view1
    group by sex
    ,case 
     when age between 18 and 21 then '18-21'
     when age between 25 and 30 then '25-30'
     when age between 35 and 40 then '35-40'
     end

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Altered:
    Code:
    CREATE FUNCTION dbo.AgeBand
        (
        @DOB AS DateTime,
        @OnThisDate AS DateTime
        )
        --RETURNS VarChar(12)
        RETURNS TinyInt
    AS
    BEGIN
    
        DECLARE @Age TinyInt
        DECLARE @AgeBand VarChar(12)
        DECLARE @OnThisMonth AS TinyInt
        DECLARE @DOBMonth AS TinyInt
     
        SELECT  @OnThisMonth = DATEPART(m, @OnThisDate)
        SELECT  @DOBMonth = DATEPART(m, @DOB)
        SELECT  @Age = DATEDIFF(yyyy, @DOB, @OnThisDate)
     
        IF (@OnThisMonth = @DOBMonth AND DATEPART(d, @OnThisDate) < DATEPART(d, @DOB)) OR @OnThisMonth < @DOBMonth BEGIN
            SELECT @Age = @Age -1
        End
        
        IF COALESCE(@Age, 0) = 0
            SELECT  @AgeBand = 'Undetermined'
        ELSE IF @Age <18
            SELECT  @AgeBand = 'Under 18'
        ELSE IF @Age <=21
            SELECT  @AgeBand = '18-21'
        ELSE IF @Age >=30
            SELECT  @AgeBand = '21-30'
        ELSE IF @Age >=40
            SELECT  @AgeBand = '35-40'
        Else
            SELECT  @AgeBand = 'Over 40'
     
        --RETURN @AgeBand
        RETURN @Age
    End
    GO
    You would need to remove the current RETURN statements and uncomment the commented out ones. As it stands this:
    Code:
    SELECT dbo.AgeBand('3 Mar 2000', '3 Mar 2004') AS Low, dbo.AgeBand('3 Mar 2000', '3 Mar 2005') AS High
    returns this:
    Low,High
    4,5
    (1 row(s) affected)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by pdreyer
    'age'=datepart(yy,getdate())-datepart(yy,DateBirth)
    - case when convert(char(8),getdate(),10) < convert(char(8),DateBirth,10)
    then 1 else 0 end
    use datediff
    Code:
    'age'=datediff(yy,DateBirth,getdate())
          - case when convert(char(8),getdate(),10) < convert(char(8),DateBirth,10)
                   then 1 else 0 end

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •