Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 10 of 121
For information on syntax and semantics, see "Analytic Functions".
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.
The following example calculates the average salary of all employees in the EMP
table:
SELECT AVG(sal) "Average" FROM emp; Average ---------- 2077.21429
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
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|