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

1. Oracle PL / SQL
2. Analytical Functions
3. Sum

Salary report with cumulative salary, without using analytic function

 



create table employee(
         emp_no                 integer         primary key
        ,lastname               varchar2(20)    not null
        ,salary                 number(3)
);

insert into employee(emp_no, lastname, salary)
             values (1,'Tom',200);

insert into employee(emp_no, lastname, salary)
             values (2,'Jack',300);

insert into employee(emp_no, lastname, salary)
             values (3,'Jason',400);

insert into employee(emp_no, lastname, salary)
             values (4,'Jane',500);


select e1.emp_no, e1.lastname, e1.salary,
        (select sum(salary)
         from employee e2
         where e2.emp_no <= e1.emp_no )
         AS cume_salary2
from employee e1 order by emp_no;


    EMP_NO LASTNAME                 SALARY CUME_SALARY2
---------- -------------------- ---------- ------------
         1 Tom                         200          200
         2 Jack                        300          500
         3 Jason                       400          900
         4 Jane                        500         1400


drop table employee;


 

Related examples in the same category