|
|
|
AVG windowing function
Syntax
AVG ( { [ALL] expression } ) OVER ( [ partition_by_clause ] order_by_clause )
Description
The aggregate function AVG returns the average of the values in a group. column_ref or expression can be of any type. The result of the function is of the same data type as that of the argument except that the result is of type INTEGER when the argument is of type SMALLINT or TINYINT. The result can have a null value.
Example
select empno, deptno, projno, sal, avg(sal) over (partition by deptno, projno order by projno) avg_proj_sal from emp;
EMPNO DEPTNO PROJNO SAL AVG_PROJ_SAL
----- ------ ------ --- -----------
7782 10 101 2450.00 2450.000000
7839 10 102 5000.00 3150.000000
7934 10 102 1300.00 3150.000000
7566 20 101 2975.00 2175.000000
7329 20 101 800.00 2175.000000
7876 20 101 1100.00 2175.000000
7788 20 101 3000.00 2175.000000
7902 20 101 3000.00 2175.000000
7698 30 101 2850.00 2850.000000
7844 30 102 1500.00 1225.000000
7900 30 102 950.00 1225.000000
7521 30 103 1250.00 1366.000000
7654 30 103 1250.00 1366.000000
7499 30 103 1600.00 1366.000000
See Also
