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;
|