Salary report with cumulative salary, using analytic function SUM : Sum « Analytical Functions « Oracle PL / SQL






Salary report with cumulative salary, using analytic function SUM

 
SQL> --
SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );

Table created.

SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,'14-Feb-2002', 23.00, '14-Feb-2002', '12 noon', 'CA',1, null, 'Gift for wife');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,'14-Feb-2003', 510.98, '14-feb-2003', '5 pm', 'NY',7, 'Rose Ted', 'Happy Valentines Day to Mother');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(3, 2,'14-Feb-2004', 315.99, '14-feb-2004', '3 pm', 'VS',2, 'Ani Forest', 'Happy Valentines Day to Father');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(4, 2,'14-Feb-1999', 191.95, '14-feb-1999', '2 pm', 'NJ',2, 'O. John', 'Happy Valentines Day');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(5, 6,'4-mar-2002', 101.95, '5-mar-2002', '2:30 pm', 'MO'   , 2, 'Cora', 'Happy Birthday from John');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(6, 9,'7-apr-2003', 221.95, '7-apr-2003', '3 pm', 'MA', 2, 'Sake Keith', 'Happy Birthday from Joe' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(7, 9,'20-jun-2004', 315.95, '21-jun-2004', '12 noon', 'BC', 2, 'Jessica Li', 'Happy Birthday from Jessica');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (8, 12, '31-dec-1999', 135.95, '1-jan-2000', '12 noon', 'DI',      3, 'Larry', 'Happy New Year from Lawrence');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (9, 12, '26-dec-2003', 715.95, '2-jan-2004', '12 noon', 'SK',7, 'Did', 'Happy Birthday from Nancy' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(10, 4, sysdate-1, 119.95, sysdate+2, '6:30 pm', 'VG',2, 'P. Jing', 'Happy Valentines Day to Jason');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(11, 2, sysdate, 310.00, sysdate+2, '3:30 pm', 'DC',2, 'C. Late', 'Happy Birthday Day to Jack');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(12, 7, sysdate-3, 121.95, sysdate-2, '1:30 pm', 'AC',2, 'W. Last', 'Happy Birthday Day to You');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(13, 7, sysdate, 211.95, sysdate-4, '4:30 pm', 'CA',2, 'J. Bond', 'Thanks for hard working');

1 row created.

SQL>
SQL> select * from ord;
  ORDER_NO    CUST_NO ORDER_DATE           TOTAL_ORDER_PRICE
---------- ---------- -------------------- -----------------
DELIVER_DATE         DELIVER PA     EMP_NO
-------------------- ------- -- ----------
DELIVER_NAME
-----------------------------------
GIFT_MESSAGE
------------------------------------------------------------------------
         1          1 14-FEB-2002 00:00:00                23
14-FEB-2002 00:00:00 12 noon CA          1

Gift for wife

         2          1 14-FEB-2003 00:00:00            510.98
14-FEB-2003 00:00:00 5 pm    NY          7
Rose Ted
Happy Valentines Day to Mother

         3          2 14-FEB-2004 00:00:00            315.99
14-FEB-2004 00:00:00 3 pm    VS          2
Ani Forest
Happy Valentines Day to Father

         4          2 14-FEB-1999 00:00:00            191.95
14-FEB-1999 00:00:00 2 pm    NJ          2
O. John
Happy Valentines Day

         5          6 04-MAR-2002 00:00:00            101.95
05-MAR-2002 00:00:00 2:30 pm MO          2
Cora
Happy Birthday from John

         6          9 07-APR-2003 00:00:00            221.95
07-APR-2003 00:00:00 3 pm    MA          2
Sake Keith
Happy Birthday from Joe

         7          9 20-JUN-2004 00:00:00            315.95
21-JUN-2004 00:00:00 12 noon BC          2
Jessica Li
Happy Birthday from Jessica

         8         12 31-DEC-1999 00:00:00            135.95
01-JAN-2000 00:00:00 12 noon DI          3
Larry
Happy New Year from Lawrence

         9         12 26-DEC-2003 00:00:00            715.95
02-JAN-2004 00:00:00 12 noon SK          7
Did
Happy Birthday from Nancy

        10          4 15-JUN-2008 17:35:22            119.95
18-JUN-2008 17:35:22 6:30 pm VG          2
P. Jing
Happy Valentines Day to Jason

        11          2 16-JUN-2008 17:35:23               310
18-JUN-2008 17:35:23 3:30 pm DC          2
C. Late
Happy Birthday Day to Jack

        12          7 13-JUN-2008 17:35:24            121.95
14-JUN-2008 17:35:24 1:30 pm AC          2
W. Last
Happy Birthday Day to You

        13          7 16-JUN-2008 17:35:24            211.95
12-JUN-2008 17:35:24 4:30 pm CA          2
J. Bond
Thanks for hard working


13 rows selected.

SQL>
SQL> select sum(total_order_price) over (order by payment_method) cume_salary
  2  from ord
  3  /
CUME_SALARY
-----------
     121.95
      437.9
     672.85
     672.85
     982.85
     1118.8
    1340.75
     1442.7
    1634.65
    2145.63
    2861.58
    2981.53
    3297.52

13 rows selected.

SQL>
SQL>
SQL> drop table ord;

Table dropped.

SQL>
SQL> --

 








Related examples in the same category

1.Sum over range
2.Sum over partition
3.Sum over partition and sum over order as running total
4.Salary report with cumulative salary, without using analytic function