By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table salary(employee_id int, employee_name varchar(10), employee_salary int);
begin
insert into salary values(1, 'John', 4000);
insert into salary values(2, 'John', 2500);
insert into salary values(4, 'John', 3400);
insert into salary values(5, 'John', 4500);
insert into salary values(6, 'John', 4300);
insert into salary values(7, 'John', 2000);
insert into salary values(8, 'John', 1200);
insert into salary values(9, 'John', 3100);
insert into salary values(11, 'John', 2600);
end;
/
1 rows affected
create table employee(employee_id int, employee_name varchar(10), manager_id int);
begin
insert into employee values(1, 'John', null);
insert into employee values(2, 'Philip', null);
insert into employee values(3, 'Rayan', 2);
insert into employee values(4, 'Peter', 2);
insert into employee values(5, 'Mark', 2);
insert into employee values(6, 'Steve', 3);
insert into employee values(7, 'Margret', 3);
insert into employee values(8, 'Paul', 3);
insert into employee values(9, 'Joe', null);
insert into employee values(10, 'Bose', 9);
insert into employee values(11, 'Jane', 9);
end;
/
1 rows affected
with cte (employee_id, employee_name, child_id) as (
select employee_id, employee_name, employee_id from employee where manager_id is null
union all
select c.employee_id, c.employee_name, e.employee_id
from employee e
inner join cte c on e.manager_id = c.child_id
)
select c.employee_id, c.employee_name, sum(s.employee_salary) total_salary
from cte c
inner join salary s on s.employee_id = c.child_id
group by c.employee_id, c.employee_name
order by c.employee_id
| EMPLOYEE_ID | EMPLOYEE_NAME | TOTAL_SALARY |
|---|---|---|
| 1 | John | 4000 |
| 2 | Philip | 17900 |
| 9 | Joe | 5700 |