Re: SQL question, calculating average age by group

Adam Sandler wrote:
> Hello,
>
> I have a question about a query. I have a personnel table. In this
> table there is a column for employee type (S_EType). I want to find
> the average age of the group of employees by each employee type.
> There
> is no age column; only a birth date column. Therefore, some
> processing
> needs to be done to determine age.
>
> At any rate, here's the SQL I wrote:
>
> select s_etype, avg ( floor((sysdate - d_birthdate)/365.25) ) from
> pass.t_personnel group by s_etype
>
> This SQL isn't mathematically correct though. For example, let's say
> there's three rows in the personnel table. The ages calculated from
> those three rows are 33, 40, and 27. The average should be 33.333...
> What I'm getting is 31.75. I believe that's because the SQL is
> taking the average of the ages as it works through the rows instead of
> taking the average of all the ages added together and divided by the
> total number of rows.
>

Hello Adam,

I was not able to reproduce this behaviour. If I have three rows with the ages of 33, 40 and 27, I get 33.3333 as average. Maybe you should post the exect dates so that I can see what's wrong.

Another tip: Your assumption on the average length of a year being 365.25 is not correct. Since every year divisible by four is a leap year (that would make 365.25) except those divisible by 100 (so you get 365.24). But years divisible by 400 are leap years again. So the correct average length of a year is 365.2425. But this should not make that big difference in your SQL.
To be on the safe side, you can use
floor(months_between(sysdate,d_birthdate)/12).

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de