Analytics - Partition clause with aggregation

View All Scripts Login to Run Script

Script Name Analytics - Partition clause with aggregation
Description SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script is the first look at the partition clause with aggregates. Run this script standalone, or take it as part of the complete Analytics class at https://tinyurl.com/devgym-classes
Area SQL General / Analytics
Contributor Connor Mcdonald (Oracle)
Created Tuesday January 12, 2016

Statement 1
```
drop table emp
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table emp (
EMPNO    NUMBER(4)    ,
ENAME             VARCHAR2(10) ,
JOB               VARCHAR2(9)  ,
MGR               NUMBER(4)    ,
HIREDATE          DATE         ,
SAL               NUMBER(7,2)  ,
COMM              NUMBER(7,2)  ,
DEPTNO            NUMBER(2)    
)

Table created.

Statement 3

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17/DEC/80','DD/MON/RR'),800,null,20)

1 row(s) inserted.

Statement 4

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20/FEB/81','DD/MON/RR'),1600,300,30)

1 row(s) inserted.

Statement 5

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22/FEB/81','DD/MON/RR'),1250,500,30)

1 row(s) inserted.

Statement 6

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02/APR/81','DD/MON/RR'),2975,null,20)

1 row(s) inserted.

Statement 7

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28/SEP/81','DD/MON/RR'),1250,1400,30)

1 row(s) inserted.

Statement 8

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01/MAY/81','DD/MON/RR'),2850,null,30)

1 row(s) inserted.

Statement 9

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09/JUN/81','DD/MON/RR'),2450,null,10)

1 row(s) inserted.

Statement 10

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('09/DEC/82','DD/MON/RR'),3000,null,20)

1 row(s) inserted.

Statement 11

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17/NOV/81','DD/MON/RR'),5000,null,10)

1 row(s) inserted.

Statement 12

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08/SEP/81','DD/MON/RR'),1500,null,30)

1 row(s) inserted.

Statement 13

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('12/JAN/83','DD/MON/RR'),1100,null,20)

1 row(s) inserted.

Statement 14

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03/DEC/81','DD/MON/RR'),950,null,30)

1 row(s) inserted.

Statement 15

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03/DEC/81','DD/MON/RR'),3000,null,20)

1 row(s) inserted.

Statement 16

Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23/JAN/82','DD/MON/RR'),1300,null,10)

1 row(s) inserted.

Statement 17

commit



select empno, ename, job, hiredate, sal 
from emp 
order by sal

ORA-02185: a token other than WORK follows COMMIT

More Details: https://docs.oracle.com/error-help/db/ora-02185

Statement 18

select deptno, empno, ename, job, sal,
      sum(sal) OVER (
        partition by deptno
        order by ename) as running_total
    from emp
    order by deptno, ename

 
DEPTNO EMPNO ENAME JOB SAL RUNNING_TOTAL
10 7782 CLARK MANAGER 2450 2450
10 7839 KING PRESIDENT 5000 7450
10 7934 MILLER CLERK 1300 8750
20 7876 ADAMS CLERK 1100 1100
20 7902 FORD ANALYST 3000 4100
20 7566 JONES MANAGER 2975 7075
20 7788 SCOTT ANALYST 3000 10075
20 7369 SMITH CLERK 800 10875
30 7499 ALLEN SALESMAN 1600 1600
30 7698 BLAKE MANAGER 2850 4450
30 7900 JAMES CLERK 950 5400
30 7654 MARTIN SALESMAN 1250 6650
30 7844 TURNER SALESMAN 1500 8150
30 7521 WARD SALESMAN 1250 9400

14 rows selected.

DEPTNO	EMPNO	ENAME	JOB	SAL	RUNNING_TOTAL
10	7782	CLARK	MANAGER	2450	2450
10	7839	KING	PRESIDENT	5000	7450
10	7934	MILLER	CLERK	1300	8750
20	7876	ADAMS	CLERK	1100	1100
20	7902	FORD	ANALYST	3000	4100
20	7566	JONES	MANAGER	2975	7075
20	7788	SCOTT	ANALYST	3000	10075
20	7369	SMITH	CLERK	800	10875
30	7499	ALLEN	SALESMAN	1600	1600
30	7698	BLAKE	MANAGER	2850	4450
30	7900	JAMES	CLERK	950	5400
30	7654	MARTIN	SALESMAN	1250	6650
30	7844	TURNER	SALESMAN	1500	8150
30	7521	WARD	SALESMAN	1250	9400

Statement 19

select deptno, empno, ename, job, sal,
      sum(sal) OVER (
        partition by deptno
        order by ename) as running_total
    from emp
    order by deptno, hiredate

 
DEPTNO EMPNO ENAME JOB SAL RUNNING_TOTAL
10 7782 CLARK MANAGER 2450 2450
10 7839 KING PRESIDENT 5000 7450
10 7934 MILLER CLERK 1300 8750
20 7369 SMITH CLERK 800 10875
20 7566 JONES MANAGER 2975 7075
20 7902 FORD ANALYST 3000 4100
20 7788 SCOTT ANALYST 3000 10075
20 7876 ADAMS CLERK 1100 1100
30 7499 ALLEN SALESMAN 1600 1600
30 7521 WARD SALESMAN 1250 9400
30 7698 BLAKE MANAGER 2850 4450
30 7844 TURNER SALESMAN 1500 8150
30 7654 MARTIN SALESMAN 1250 6650
30 7900 JAMES CLERK 950 5400

14 rows selected.

DEPTNO	EMPNO	ENAME	JOB	SAL	RUNNING_TOTAL
10	7782	CLARK	MANAGER	2450	2450
10	7839	KING	PRESIDENT	5000	7450
10	7934	MILLER	CLERK	1300	8750
20	7369	SMITH	CLERK	800	10875
20	7566	JONES	MANAGER	2975	7075
20	7902	FORD	ANALYST	3000	4100
20	7788	SCOTT	ANALYST	3000	10075
20	7876	ADAMS	CLERK	1100	1100
30	7499	ALLEN	SALESMAN	1600	1600
30	7521	WARD	SALESMAN	1250	9400
30	7698	BLAKE	MANAGER	2850	4450
30	7844	TURNER	SALESMAN	1500	8150
30	7654	MARTIN	SALESMAN	1250	6650
30	7900	JAMES	CLERK	950	5400

Statement 20

select deptno, job, ename, sal,
           sum(sal) over () total_sal,
           sum(sal) over 
               ( partition by deptno) sal_by_dept,
           sum(sal) over 
               ( partition by deptno, job) sal_by_dept_job
    from emp
    order by deptno, job

 
DEPTNO JOB ENAME SAL TOTAL_SAL SAL_BY_DEPT SAL_BY_DEPT_JOB
10 CLERK MILLER 1300 29025 8750 1300
10 MANAGER CLARK 2450 29025 8750 2450
10 PRESIDENT KING 5000 29025 8750 5000
20 ANALYST SCOTT 3000 29025 10875 6000
20 ANALYST FORD 3000 29025 10875 6000
20 CLERK ADAMS 1100 29025 10875 1900
20 CLERK SMITH 800 29025 10875 1900
20 MANAGER JONES 2975 29025 10875 2975
30 CLERK JAMES 950 29025 9400 950
30 MANAGER BLAKE 2850 29025 9400 2850
30 SALESMAN MARTIN 1250 29025 9400 5600
30 SALESMAN WARD 1250 29025 9400 5600
30 SALESMAN ALLEN 1600 29025 9400 5600
30 SALESMAN TURNER 1500 29025 9400 5600

14 rows selected.

DEPTNO	JOB	ENAME	SAL	TOTAL_SAL	SAL_BY_DEPT	SAL_BY_DEPT_JOB
10	CLERK	MILLER	1300	29025	8750	1300
10	MANAGER	CLARK	2450	29025	8750	2450
10	PRESIDENT	KING	5000	29025	8750	5000
20	ANALYST	SCOTT	3000	29025	10875	6000
20	ANALYST	FORD	3000	29025	10875	6000
20	CLERK	ADAMS	1100	29025	10875	1900
20	CLERK	SMITH	800	29025	10875	1900
20	MANAGER	JONES	2975	29025	10875	2975
30	CLERK	JAMES	950	29025	9400	950
30	MANAGER	BLAKE	2850	29025	9400	2850
30	SALESMAN	MARTIN	1250	29025	9400	5600
30	SALESMAN	WARD	1250	29025	9400	5600
30	SALESMAN	ALLEN	1600	29025	9400	5600
30	SALESMAN	TURNER	1500	29025	9400	5600

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database