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!

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
12/01/2018 11:47 - I am sure you are welcome. -- Mark --

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!

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

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 --

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below