AVG

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

AVG returns average value of expr. You can use it as an aggregate or analytic function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

See Also: "Aggregate Functions".

Aggregate Example

The following example calculates the average salary of all employees in the EMP table:

SELECT AVG(sal) "Average" FROM emp;

   Average
----------
2077.21429

Analytic Example

The following example calculates, for each employee in the EMP table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee:

SELECT mgr, ename, hiredate, sal,
   AVG(sal) OVER (PARTITION BY mgr ORDER BY hiredate 
   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
   FROM emp;

       MGR ENAME      HIREDATE         SAL     C_MAVG
---------- ---------- --------- ---------- ----------
      7566 FORD       03-DEC-81       3000       3000
      7566 SCOTT      19-APR-87       3000       3000
      7698 ALLEN      20-FEB-81       1600       1425
      7698 WARD       22-FEB-81       1250       1450
      7698 TURNER     08-SEP-81       1500 1333.33333
      7698 MARTIN     28-SEP-81       1250 1233.33333
      7698 JAMES      03-DEC-81        950       1100
      7782 MILLER     23-JAN-82       1300       1300
      7788 ADAMS      23-MAY-87       1100       1100
      7839 JONES      02-APR-81       2975     2912.5
      7839 BLAKE      01-MAY-81       2850 2758.33333
      7839 CLARK      09-JUN-81       2450       2650
      7902 SMITH      17-DEC-80        800        800
           KING       17-NOV-81       5000       5000