**SQL question, calculating average age by group**

12/01/2018 11:47

Hello, I have a question about a query. I have a personnel table. In thistable there is a column for employee type (S_EType). I want to findthe average age of the group of employees by each employee type. Thereis no age column; only a birth date column. Therefore, some processingneeds to be done to determine age.

select s_etype, avg ( floor((sysdate - d_birthdate)/365.25) ) frompass.t_personnel group by s_etype This SQL isn't mathematically correct though. For example, let's saythere's three rows in the personnel table. The ages calculated fromthose 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 istaking the average of the ages as it works through the rows instead oftaking the average of all the ages added together and divided by thetotal number of rows.

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

Thanks!

select s_etype, avg ( floor((sysdate - d_birthdate)/365.25) ) frompass.t_personnel group by s_etype This SQL isn't mathematically correct though. For example, let's saythere's three rows in the personnel table. The ages calculated fromthose 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 istaking the average of the ages as it works through the rows instead oftaking the average of all the ages added together and divided by thetotal number of rows.

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

Thanks!

*Source is Usenet: comp.databases.oracle.server*

**Sign in to add a comment**

**Answer score: 5**

*Source is Usenet: comp.databases.oracle.server*

**Sign in to add a comment**

**Answer score: 5**

12/01/2018 11:47 - Gents... thanks for the help so far! I did know going into it that then / 365.25 was incorrect but it was something I tried to crank outreally quickly and hence the mathmatical approximation.

When I started seeing some of the optput later on though, that littlevoice inside my head said something was wrong... so I decided to askthis group for insight.

Also, I tried using this SQL statement: select s_etype, avg (floor((months_between(sysdate,d_birthdate)/12))from pass.t_personnel group by s_etype And I keep getting missing right parenthesis errors (the asterikappears underneath the from). I'm wondering if its because of themonths_between function? I'm not familiar with months_between... is ita provided function like 'floor''? Thanks!

When I started seeing some of the optput later on though, that littlevoice inside my head said something was wrong... so I decided to askthis group for insight.

Also, I tried using this SQL statement: select s_etype, avg (floor((months_between(sysdate,d_birthdate)/12))from pass.t_personnel group by s_etype And I keep getting missing right parenthesis errors (the asterikappears underneath the from). I'm wondering if its because of themonths_between function? I'm not familiar with months_between... is ita provided function like 'floor''? Thanks!

*Source is Usenet: comp.databases.oracle.server*

**Sign in to add a comment**

**Answer score: 5**

12/01/2018 11:47 - Hello Adam, I was not able to reproduce this behaviour. If I have three rows withthe 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 being365.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 get365.24). But years divisible by 400 are leap years again. So the correctaverage length of a year is 365.2425. But this should not make that bigdifference in your SQL.

To be on the safe side, you can usefloor(months_between(sysdate,d_birthdate)/12).

Hope that helps,Lothar

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 being365.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 get365.24). But years divisible by 400 are leap years again. So the correctaverage length of a year is 365.2425. But this should not make that bigdifference in your SQL.

To be on the safe side, you can usefloor(months_between(sysdate,d_birthdate)/12).

Hope that helps,Lothar

*Source is Usenet: comp.databases.oracle.server*

**Sign in to add a comment**

**Answer score: 5**

12/01/2018 11:47 - Gents... thanks for your help... I went and googled months_between andgot my answer (and fixed the SQL so there's no missing right parenerrors). Thanks for introducing me to that method. I'm getting betterresults now!

*Source is Usenet: comp.databases.oracle.server*

**Sign in to add a comment**

**Answer score: 5**

12/01/2018 11:47 - Adam, Lothar is correct in that actual data would be necessary forsomeone to be able to reproduce your problem. Lothar provided a betteryears calculation.

You should post back if you have solved your problem or with sampledata and the SQL if you still need help.

HTH -- Mark D Powell --

You should post back if you have solved your problem or with sampledata and the SQL if you still need help.

HTH -- Mark D Powell --

*Source is Usenet: comp.databases.oracle.server*

**Sign in to add a comment**

Hide commentsHide comments