CODEWITHSUNDEEP
sqloracle-database
Brandon W
Brandon W

Reputation: 3

Trying to find out the average length of employment for all employees

And round it to the nearest year.

select employee_id, months_between(start_date, sysdate) 
from job_history;

So far I've been able to calculate the number of months.

I am working with 12c

Upvotes: 0

Views: 1058

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If you want the average, then you need aggregation:

select avg(months_between(sysdate, start_date) ) as avg_months
from job_history;

If you want it in years:

select round(avg(months_between(sysdate, start_date))/12) as avg_years
from job_history;

Upvotes: 1

marcothesane
marcothesane

Reputation: 6731

Try this:

SELECT 
  employee_id
, AVG(CAST( months_between(start_date, sysdate) / 12 AS INTEGER) AS avg_work_years
FROM job_history

Upvotes: 0

user3272686
user3272686

Reputation: 849

This is what it would look like

SELECT AVG(DATEDIFF(year,start_date, end_date)) AS AverageLengthOfEmployment from job_history

Upvotes: 0

Related Questions