partition clause : PARTITION « Analytical Functions « Oracle PL / SQL

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

partition clause

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


insert into employee(no, lastname, salary,dept_no)
             values (1,'Tom', 400,1);

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

insert into employee(no, lastname, salary,dept_no)
             values (3,'Lee', 200,3);

insert into employee(no, lastname, salary,dept_no)
             values (4,'Smith', 100,4);


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


LASTNAME                DEPT_NO     SALARY TOTAL_SALARY
-------------------- ---------- ---------- ------------
Tom                           1        400          400
Jack                          2        300          300
Lee                           3        200          200
Smith                         4        100          100


drop table employee;


   
  

Related examples in the same category