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