sum over partition by, order by : OVER « Analytical Functions « Oracle PL/SQL Tutorial

SQL>
SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );

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> break on deptno skip 1
SQL> select deptno,
  2         ename,
  3         sal,
  4         sum(sal) over (partition by deptno order by sal) CumDeptTot,
  5         sum(sal) over (partition by deptno) SalByDept,
  6         sum(sal) over (order by deptno, sal) CumTot,
  7         sum(sal) over () TotSal
  8    from emp
  9   order by deptno, sal
 10  /

    DEPTNO ENAME             SAL CUMDEPTTOT  SALBYDEPT     CUMTOT     TOTSAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 MILLER           1300       1300       8750       1300      29025
           CLARK            2450       3750       8750       3750      29025
           KING             5000       8750       8750       8750      29025

        20 SMITH             800        800      10875       9550      29025
           ADAMS            1100       1900      10875      10650      29025
           JONES            2975       4875      10875      13625      29025
           SCOTT            3000      10875      10875      19625      29025
           FORD             3000      10875      10875      19625      29025

        30 JAMES             950        950       9400      20575      29025

    DEPTNO ENAME             SAL CUMDEPTTOT  SALBYDEPT     CUMTOT     TOTSAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
        30 MARTIN           1250       3450       9400      23075      29025
           WARD             1250       3450       9400      23075      29025
           TURNER           1500       4950       9400      24575      29025
           ALLEN            1600       6550       9400      26175      29025
           BLAKE            2850       9400       9400      29025      29025


14 rows selected.

SQL>
SQL>
SQL>
SQL> drop table emp;

Table dropped.

16.20.OVER
	16.20.1.	AVG(salary) OVER
	16.20.2.	ROUND(AVG(salary) OVER())
	16.20.3.	Use partitioning in the OVER clause of the aggregate-analytical function
	16.20.4.	Ratio-to-Report
	16.20.5.	Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
	16.20.6.	Using Analytic Functions: AVG(Mark) OVER
	16.20.7.	Using Analytic Functions: AVG(Mark) OVER (ORDER BY StudentID, Mark)
	16.20.8.	Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	16.20.9.	sum over (nothing)
	16.20.10.	sum over partition by, order by
	16.20.11.	sum salary over PARTITION BY