Performing a Cumulative Sum : Cumulative Sum « Analytical Functions « Oracle PL / SQL






Performing a Cumulative Sum


SQL> CREATE TABLE all_sales (
  2    year INTEGER,
  3    month INTEGER,
  4    prd_type_id INTEGER,
  5    emp_id INTEGER ,
  6    amount NUMBER(8, 2)
  7  );

Table created.

SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,1    ,1          ,21    ,16034.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,2    ,1          ,21    ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,3    ,2          ,21    ,20167.83);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,4    ,2          ,21    ,25056.45);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,5    ,2          ,21    ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,6    ,1          ,21    ,15564.66);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,7    ,1          ,21    ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,8    ,1          ,21    ,16434.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,9    ,1          ,21    ,19654.57);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,10   ,1          ,21    ,21764.19);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,11   ,1          ,21    ,13026.73);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2006,12   ,2          ,21    ,10034.64);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,1    ,2          ,22    ,16634.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,1    ,2          ,21    ,26034.84);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,2    ,1          ,21    ,12644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,3    ,1          ,21    ,NULL);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,4    ,1          ,21    ,25026.45);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,5    ,1          ,21    ,17212.66);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,6    ,1          ,21    ,15564.26);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,7    ,2          ,21    ,62654.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,8    ,2          ,21    ,26434.82);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,9    ,2          ,21    ,15644.65);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,10   ,2          ,21    ,21264.19);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,11   ,1          ,21    ,13026.73);

1 row created.

SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
  2                  values(2005,12   ,1          ,21    ,10032.64);

1 row created.

SQL>
SQL> select * from all_sales;

      YEAR      MONTH PRD_TYPE_ID     EMP_ID     AMOUNT
---------- ---------- ----------- ---------- ----------
      2006          1           1         21   16034.84
      2006          2           1         21   15644.65
      2006          3           2         21   20167.83
      2006          4           2         21   25056.45
      2006          5           2         21
      2006          6           1         21   15564.66
      2006          7           1         21   15644.65
      2006          8           1         21   16434.82
      2006          9           1         21   19654.57
      2006         10           1         21   21764.19
      2006         11           1         21   13026.73
      2006         12           2         21   10034.64
      2005          1           2         22   16634.84
      2005          1           2         21   26034.84
      2005          2           1         21   12644.65
      2005          3           1         21
      2005          4           1         21   25026.45
      2005          5           1         21   17212.66
      2005          6           1         21   15564.26
      2005          7           2         21   62654.82
      2005          8           2         21   26434.82
      2005          9           2         21   15644.65
      2005         10           2         21   21264.19
      2005         11           1         21   13026.73
      2005         12           1         21   10032.64

25 rows selected.

SQL>
SQL> --Performing a Cumulative Sum
SQL>
SQL> SELECT
  2   month, SUM(amount) AS month_amount,
  3   SUM(SUM(amount)) OVER
  4    (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  5    AS cumulative_amount
  6  FROM all_sales
  7  GROUP BY month
  8  ORDER BY month;

     MONTH MONTH_AMOUNT CUMULATIVE_AMOUNT
---------- ------------ -----------------
         1     58704.52          58704.52
         2      28289.3          86993.82
         3     20167.83         107161.65
         4      50082.9         157244.55
         5     17212.66         174457.21
         6     31128.92         205586.13
         7     78299.47          283885.6
         8     42869.64         326755.24
         9     35299.22         362054.46
        10     43028.38         405082.84
        11     26053.46          431136.3
        12     20067.28         451203.58

12 rows selected.

SQL>
SQL>
SQL> drop table all_sales;

Table dropped.

SQL>
SQL>

           
       








Related examples in the same category

1.The use of ROWS UNBOUNDED PRECEDING to implicitly indicate the end of the window is
2.cumulative total_order_price for all orders.
3.Display a cumulative total_order_price for all orders(Partition by the truncated order_date
4.Add an analytic order by clause
5.Display cumulative total_order_price and cumulative by customer
6.Sum over partition demo
7.Sum over and order by