Cumulative Sum

You can perform window function with the following functions: SUM(), AVG(), MAX(), MIN(), COUNT(), VARIANCE(), and STDDEV().

The following query performs a cumulative sum to compute the cumulative salaries amount, starting with each department.

SUM(amount) computes the sum of an amount. The outer SUM() computes the cumulative amount. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines the start and end of the window. The start is set to UNBOUNDED PRECEDING. UNBOUNDED PRECEDING means that the start of the window is fixed at the first row in the result set. CURRENT ROW sets the end of the window. CURRENT ROW represents the current row in the result set.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> SELECT deptno, SUM(sal) AS dept_salary,
  2  SUM(SUM(sal)) OVER (ORDER BY deptno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  3  AS cumulative_amount
  4  FROM emp
  5  GROUP BY deptno
  6  ORDER BY deptno;

    DEPTNO DEPT_SALARY CUMULATIVE_AMOUNT
---------- ----------- -----------------
        10        5850              5850
        20        8275             14125
        30        8450             22575

SQL>

The following query uses a cumulative sum to compute the cumulative salary amount, starting with employee number 2 to 6:


SQL> SELECT deptno, SUM(sal) AS month_amount,
  2  SUM(SUM(sal)) OVER
  3  (ORDER BY deptno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
  4  cumulative_amount
  5  FROM emp
  6  WHERE empno  BETWEEN 2 AND 6
  7  GROUP BY deptno
  8  ORDER BY deptno;

    DEPTNO MONTH_AMOUNT CUMULATIVE_AMOUNT
---------- ------------ -----------------
        20         2975              2975
        30         6950              9925

SQL>

Home »
Oracle »
Analytical Functions »

Window Functions: