SUM Analytic Function
This article gives an overview of the SUM analytic function. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Setup
SUM as an Aggregate Function
SUM Analytic Function
Quick Links
Related articles.
Analytic Functions : All Articles
Setup
The examples in this article require the following table.
--DROP TABLE emp PURGE;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
SUM as an Aggregate Function
The SUM aggregate function returns the sum of the specified values in a set. As an aggregate function it reduces the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated value. In the following example we see the total value of the salaries for all employees.
SELECT SUM(sal) AS sum_total
FROM emp;
SUM_TOTAL
----------
29025
SQL>
We can get more granularity of information by including a GROUP BY clause. In the following example we see the sum of the salaires on a per-department basis.
SELECT deptno,
SUM(sal) AS sum_total_by_dept
FROM emp
GROUP BY deptno
ORDER BY deptno;
DEPTNO SUM_TOTAL_BY_DEPT
---------- -----------------
10 8750
20 10875
30 9400
SQL>
In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.
SUM Analytic Function
The basic description for the SUM analytic function is shown below. The analytic clause is described in more detail here.
SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause)
Omitting a partitioning clause from the OVER clause means the whole result set is treated as a single partition. In the following example we display the total salaries of all employees, as well as all the original data.
SELECT empno,
ename,
deptno,
sal,
SUM(sal) OVER () AS total_sal
FROM emp;
EMPNO ENAME DEPTNO SAL TOTAL_SAL
---------- ---------- ---------- ---------- ----------
7369 SMITH 20 800 29025
7499 ALLEN 30 1600 29025
7521 WARD 30 1250 29025
7566 JONES 20 2975 29025
7654 MARTIN 30 1250 29025
7698 BLAKE 30 2850 29025
7782 CLARK 10 2450 29025
7788 SCOTT 20 3000 29025
7839 KING 10 5000 29025
7844 TURNER 30 1500 29025
7876 ADAMS 20 1100 29025
7900 JAMES 30 950 29025
7902 FORD 20 3000 29025
7934 MILLER 10 1300 29025
SQL>
Adding the partitioning clause allows us to display total salary within a partition.
SELECT empno,
ename,
deptno,
sal,
SUM(sal) OVER (PARTITION BY deptno) AS total_sal_by_dept
FROM emp;
EMPNO ENAME DEPTNO SAL TOTAL_SAL_BY_DEPT
---------- ---------- ---------- ---------- -----------------
7782 CLARK 10 2450 8750
7839 KING 10 5000 8750
7934 MILLER 10 1300 8750
7566 JONES 20 2975 10875
7902 FORD 20 3000 10875
7876 ADAMS 20 1100 10875
7369 SMITH 20 800 10875
7788 SCOTT 20 3000 10875
7521 WARD 30 1250 9400
7844 TURNER 30 1500 9400
7499 ALLEN 30 1600 9400
7900 JAMES 30 950 9400
7698 BLAKE 30 2850 9400
7654 MARTIN 30 1250 9400
SQL>
Adding the ORDER BY clause allows us to display a running total salary within a partition. In the example below, the default windowing clause is used, as well as being specified explicitly.
SELECT empno,
ename,
deptno,
sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) AS running_tot_sal_by_dept_1,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_tot_sal_by_dept_2
FROM emp;
EMPNO ENAME DEPTNO SAL RUNNING_TOT_SAL_BY_DEPT_1 RUNNING_TOT_SAL_BY_DEPT_2
---------- ---------- ---------- ---------- ------------------------- -------------------------
7934 MILLER 10 1300 1300 1300
7782 CLARK 10 2450 3750 3750
7839 KING 10 5000 8750 8750
7369 SMITH 20 800 800 800
7876 ADAMS 20 1100 1900 1900
7566 JONES 20 2975 4875 4875
7788 SCOTT 20 3000 10875 10875
7902 FORD 20 3000 10875 10875
7900 JAMES 30 950 950 950
7654 MARTIN 30 1250 3450 3450
7521 WARD 30 1250 3450 3450
7844 TURNER 30 1500 4950 4950
7499 ALLEN 30 1600 6550 6550
7698 BLAKE 30 2850 9400 9400
SQL>
This article gives an overview of the SUM analytic function. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Setup
SUM as an Aggregate Function
SUM Analytic Function
Quick Links
Related articles.
Analytic Functions : All Articles
Setup
The examples in this article require the following table.
--DROP TABLE emp PURGE;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
SUM as an Aggregate Function
The SUM aggregate function returns the sum of the specified values in a set. As an aggregate function it reduces the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated value. In the following example we see the total value of the salaries for all employees.
SELECT SUM(sal) AS sum_total
FROM emp;
SUM_TOTAL
----------
29025
SQL>
We can get more granularity of information by including a GROUP BY clause. In the following example we see the sum of the salaires on a per-department basis.
SELECT deptno,
SUM(sal) AS sum_total_by_dept
FROM emp
GROUP BY deptno
ORDER BY deptno;
DEPTNO SUM_TOTAL_BY_DEPT
---------- -----------------
10 8750
20 10875
30 9400
SQL>
In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.
SUM Analytic Function
The basic description for the SUM analytic function is shown below. The analytic clause is described in more detail here.
SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause)
Omitting a partitioning clause from the OVER clause means the whole result set is treated as a single partition. In the following example we display the total salaries of all employees, as well as all the original data.
SELECT empno,
ename,
deptno,
sal,
SUM(sal) OVER () AS total_sal
FROM emp;
EMPNO ENAME DEPTNO SAL TOTAL_SAL
---------- ---------- ---------- ---------- ----------
7369 SMITH 20 800 29025
7499 ALLEN 30 1600 29025
7521 WARD 30 1250 29025
7566 JONES 20 2975 29025
7654 MARTIN 30 1250 29025
7698 BLAKE 30 2850 29025
7782 CLARK 10 2450 29025
7788 SCOTT 20 3000 29025
7839 KING 10 5000 29025
7844 TURNER 30 1500 29025
7876 ADAMS 20 1100 29025
7900 JAMES 30 950 29025
7902 FORD 20 3000 29025
7934 MILLER 10 1300 29025
SQL>
Adding the partitioning clause allows us to display total salary within a partition.
SELECT empno,
ename,
deptno,
sal,
SUM(sal) OVER (PARTITION BY deptno) AS total_sal_by_dept
FROM emp;
EMPNO ENAME DEPTNO SAL TOTAL_SAL_BY_DEPT
---------- ---------- ---------- ---------- -----------------
7782 CLARK 10 2450 8750
7839 KING 10 5000 8750
7934 MILLER 10 1300 8750
7566 JONES 20 2975 10875
7902 FORD 20 3000 10875
7876 ADAMS 20 1100 10875
7369 SMITH 20 800 10875
7788 SCOTT 20 3000 10875
7521 WARD 30 1250 9400
7844 TURNER 30 1500 9400
7499 ALLEN 30 1600 9400
7900 JAMES 30 950 9400
7698 BLAKE 30 2850 9400
7654 MARTIN 30 1250 9400
SQL>
Adding the ORDER BY clause allows us to display a running total salary within a partition. In the example below, the default windowing clause is used, as well as being specified explicitly.
SELECT empno,
ename,
deptno,
sal,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) AS running_tot_sal_by_dept_1,
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_tot_sal_by_dept_2
FROM emp;
EMPNO ENAME DEPTNO SAL RUNNING_TOT_SAL_BY_DEPT_1 RUNNING_TOT_SAL_BY_DEPT_2
---------- ---------- ---------- ---------- ------------------------- -------------------------
7934 MILLER 10 1300 1300 1300
7782 CLARK 10 2450 3750 3750
7839 KING 10 5000 8750 8750
7369 SMITH 20 800 800 800
7876 ADAMS 20 1100 1900 1900
7566 JONES 20 2975 4875 4875
7788 SCOTT 20 3000 10875 10875
7902 FORD 20 3000 10875 10875
7900 JAMES 30 950 950 950
7654 MARTIN 30 1250 3450 3450
7521 WARD 30 1250 3450 3450
7844 TURNER 30 1500 4950 4950
7499 ALLEN 30 1600 6550 6550
7698 BLAKE 30 2850 9400 9400
SQL>
No comments:
Post a Comment