8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
Related 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>
The RANGE BETWEEN
windowing clause is a reporting range, so all rows of the same value are included, which makes the running totals look wrong, if that's not what you were expecting. If we switch to the ROWS BETWEEN
windowing clause, you might get the result you were expecting. look at the comparison between the results of the first call using the default windowing clause, and the explicit windowing clause using ROWS BETWEEN
below.
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 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_running_tot_sal_by_dept_2 FROM emp; EMPNO ENAME DEPTNO SAL RUNNING_TOT_SAL_BY_DEPT_1 ROW_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 7875 7902 FORD 20 3000 10875 10875 7900 JAMES 30 950 950 950 7654 MARTIN 30 1250 3450 2200 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>
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
Hope this helps. Regards Tim...