2 Replies Latest reply on Nov 26, 2012 1:29 PM by Peter vd Zwan

# AVG function

Why we get result for "select avg(sysdate-hire_date) from employees;" & error for "select avg(hire_date) from employees;" ?
• ###### 1. Re: AVG function
It's because the result of the DATE subtraction is a numeric data type which an average can be computed on:
``````SQL> SELECT DUMP(AVG(SYSDATE - TO_DATE('01/01/2012','MM/DD/YYYY'))) FROM DUAL;

DUMP(AVG(SYSDATE-TO_DATE('01/01/2012','MM/DD/YYYY')))
------------------------------------------------------------------------------
Typ=2 Len=21: 194,4,31,35,50,77,86,19,52,86,19,52,86,19,52,86,19,52,86,19,53

1 row selected.

SQL> SELECT dump(1) FROM Dual;

DUMP(1)
------------------
Typ=2 Len=2: 193,2

1 row selected.``````
However, trying to calculate an average on just a DATE is meaningless and therefore isn't allowed.
• ###### 2. Re: AVG function
Hi,

Oracle can not take the average of some dates.
But you can do the following:
``````with employees as
(
select date '2011-11-30' hire_date from dual
union all select date '2011-11-29' hire_date from dual
union all select date '2011-11-28' hire_date from dual
union all select date '2011-11-27' hire_date from dual
union all select date '2011-11-26' hire_date from dual
)

select
avg(trunc(sysdate) - hire_date)                       avg_days_back
,trunc(sysdate) - avg(trunc(sysdate) - hire_date)     avg_date

from
employees
;

AVG_DAYS_BACK AVG_DATE
------------- ---------
364 28-NOV-11 ``````
Regards,

Peter