Sum over and order by : Cumulative Sum « Analytical Functions « Oracle PL / SQL

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

Sum over and order by

 



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

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

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

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

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


select lastname , dept_no, salary,
         sum(salary) over (order by dept_no, lastname) running_total
        ,sum(salary) over (partition by dept_no order by lastname) dept_total
    from employee
    order by dept_no, lastname;


LASTNAME                DEPT_NO     SALARY RUNNING_TOTAL DEPT_TOTAL
-------------------- ---------- ---------- ------------- ----------
Jane                          2        500           500        500
Tom                           2        200           700        700
Jack                          3        300          1000        300
Jason                         3        400          1400        700


drop table employee;

 

Related examples in the same category