Thursday, September 26, 2019

Oracle SUM Analytic Function

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>

No comments:

Post a Comment