Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Analytical Functions

 

Sum over partition by, order by

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> drop table emp; Table dropped.