2 Replies Latest reply: Nov 26, 2012 7:29 AM by Peter vd Zwan RSS

    AVG function

    975716
      Why we get result for "select avg(sysdate-hire_date) from employees;" & error for "select avg(hire_date) from employees;" ?
        • 1. Re: AVG function
          Centinul
          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
            Peter vd Zwan
            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