Post reply

calculating average age

  • August 16, 2007 at 7:27 am

    #180314

    Hello

    what is the best way to calculate in a query the average age of the persons in a table, having the date of birth, and taking into consideration that some date or birth are NULL. So i dont want the average to count them.

    thanks!

  • August 16, 2007 at 7:43 am

    #727324

    DECLARE @today datetime

    SELECT @today = GETDATE()

    SELECT AVG(FLOOR(DATEDIFF(day, dob, @today) / 365.2425)) FROM ages

    WHERE dob IS NOT NULL

    (assuming that the table is called "ages", and the column with the date of birth is called "dob".

    This is of course just one solution, not sure if this is the "best"

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 16, 2007 at 8:02 am

    #727333

    Thanks Andras!

    why do I have the feeling that it should be

    FLOOR(AVG(...

    and not

    AVG(FLOOR(...

    ?

    again... just a feeling

     

  • August 16, 2007 at 8:32 am

    #727360

    It depends on what you would like to count

    AVG(FLOOR will give you the average of ages expressed in years, so if someone is 30 years and 6 months, the 6 months will not be considered.

    FLOOR(AVG will consider the extra 6 months, but then it will get rid of the extra in the average.

    The results can be different. FLOOR(AVG may be more. If you have 2 people, and one is 30.9, the other is 31.9, the

    FLOOR(AVG will be FLOOR((30.9+31.9)/2)=FLOOR(31.4) = 31

    while the

    AVG(FLOOR will give you 30.0 years

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 16, 2007 at 8:42 am

    #727366

    Of course if one wants to use AVG(FLOOR, the query can be simplified to:

    DECLARE @today datetime

    SELECT @today = GETDATE()

    SELECT AVG(DATEDIFF(year, dob, @today)) from ages

    WHERE dob IS NOT NULL

    (and I'm getting a bit carried away with this problem )

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 16, 2007 at 8:49 am

    #727371

    Great!

    Thanks again (for getting carried away)!

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply