SQL question, calculating average age by group

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.

At any rate, any help regarding where I'm going wrong with this query is greatly appreciated.

Thanks! Received on Wed Aug 24 2005 - 10:13:28 CDT