8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

AVG * BIT_AND_AGG * BIT_OR_AGG * BIT_XOR_AGG * CHECKSUM *
CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_SET CORR *
COUNT * COVAR_POP * COVAR_SAMP * CUME_DIST DENSE_RANK
FEATURE_DETAILS FEATURE_ID FEATURE_SET FEATURE_VALUE FIRST
FIRST_VALUE * KURTOSIS_POP * KURTOSIS_SAMP * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION
PREDICTION_COST PREDICTION_DETAILS PREDICTION_PROBABILITY PREDICTION_SET RANK
RATIO_TO_REPORT REGR_ (Linear Regression) Functions * ROW_NUMBER SKEWNESS_POP * SKEWNESS_SAMP *
STDDEV * STDDEV_POP * STDDEV_SAMP * SUM * VAR_POP *
VAR_SAMP * VARIANCE * String Aggregation Top-N Queries  

For more information see:

Hope this helps. Regards Tim...

Back to the Top.