Mega Code Archive

 

Categories / Oracle PLSQL / Analytical Functions

 

Avg over and avg over order by

SQL> SQL> set echo on SQL> set numformat 99999.99 SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,   2                    ENAME VARCHAR2(10),   3                    JOB VARCHAR2(9),   4                    MGR NUMBER(4),   5                    HIREDATE DATE,   6                    SAL NUMBER(7, 2),   7                    COMM NUMBER(7, 2),   8                    DEPTNO NUMBER(2)); Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select ename, sal, avg(sal) over ()   2  from emp   3  / ENAME            SAL AVG(SAL)OVER() ---------- --------- -------------- SMITH         800.00        2073.21 ALLEN        1600.00        2073.21 WARD         1250.00        2073.21 JONES        2975.00        2073.21 MARTIN       1250.00        2073.21 BLAKE        2850.00        2073.21 CLARK        2450.00        2073.21 SCOTT        3000.00        2073.21 KING         5000.00        2073.21 TURNER       1500.00        2073.21 ADAMS        1100.00        2073.21 ENAME            SAL AVG(SAL)OVER() ---------- --------- -------------- JAMES         950.00        2073.21 FORD         3000.00        2073.21 MILLER       1300.00        2073.21 14 rows selected. SQL> SQL> select ename, sal, avg(sal)  over ( ORDER BY ENAME )   2  from emp   3  order by ename   4  / ENAME            SAL AVG(SAL)OVER(ORDERBYENAME) ---------- --------- -------------------------- ADAMS        1100.00                    1100.00 ALLEN        1600.00                    1350.00 BLAKE        2850.00                    1850.00 CLARK        2450.00                    2000.00 FORD         3000.00                    2200.00 JAMES         950.00                    1991.67 JONES        2975.00                    2132.14 KING         5000.00                    2490.63 MARTIN       1250.00                    2352.78 MILLER       1300.00                    2247.50 SCOTT        3000.00                    2315.91 ENAME            SAL AVG(SAL)OVER(ORDERBYENAME) ---------- --------- -------------------------- SMITH         800.00                    2189.58 TURNER       1500.00                    2136.54 WARD         1250.00                    2073.21 14 rows selected. SQL> SQL> drop table emp; Table dropped. SQL>