Calculate sum of current row and child rows in hierarchical WITH query in Oracle

A database approach would be a recursive CTE that first list all emps along their salaries.

In the recursive part you add for each empa new record with the related person (REL_EMPNO) for all their direct and indirect managers.

You get something like this (I'm using only subset of the data)

with dt(EMPNO, REP_EMPNO, REL_ENAME, SAL, MGR) as (
select  EMPNO, EMPNO, ENAME, SAL, MGR
from emp 
union all
select dt.EMPNO, emp.EMPNO, emp.ENAME, dt.SAL, emp.MGR
from emp
join dt on emp.empno = dt.mgr
)
select 
 EMPNO, REP_EMPNO, REL_ENAME, SAL
from dt order by 1,2;

     EMPNO  REP_EMPNO REL_EN        SAL
---------- ---------- ------ ----------
      7499       7499 ALLEN        1600
      7499       7698 BLAKE        1600
      7499       7839 KING         1600
      7654       7654 MARTIN       1250
      7654       7698 BLAKE        1250
      7654       7839 KING         1250
      7698       7698 BLAKE        2850
      7698       7839 KING         2850
      7839       7839 KING         5000
      7900       7698 BLAKE         950
      7900       7839 KING          950
      7900       7900 JAMES         950

So for example you see that the salary of ALLEN (1600) is related to him and also to BLAKE and KING.

This is nearly done only remains to group by the related person and sum the salary.

with dt(EMPNO, REP_EMPNO, REL_ENAME, SAL, MGR) as (
select  EMPNO, EMPNO, ENAME, SAL, MGR
from emp 
union all
select dt.EMPNO, emp.EMPNO, emp.ENAME, dt.SAL, emp.MGR
from emp
join dt on emp.empno = dt.mgr
)
select 
 REP_EMPNO EMPNO, REL_ENAME ENAME, sum(SAL) SAL
from dt 
group by REP_EMPNO, REL_ENAME
order by 1;

     EMPNO ENAME         SAL
---------- ------ ----------
      7499 ALLEN        1600
      7654 MARTIN       1250
      7698 BLAKE        6650
      7839 KING        11650
      7900 JAMES         950

If on the contrary you have a more program developer background you may find usefull approach in defining a recursive PL/SQL function, that returns the salary of a person if the person have no childs. Otherwise the function calls itself recursively to add the sum of salaries of all the childs.

create or replace function get_hir_sal(p_empno int) return number as
v_tot_child int;
v_own_sal NUMBER;
v_child_sal NUMBER;
begin
  /* check if child noted exists */
  select count(*) into v_tot_child 
  from emp
  where mgr = p_empno;
  /* own salary */
  select sal into v_own_sal 
  from emp
  where empno = p_empno;  
  
  if v_tot_child = 0 then
     return(v_own_sal);
  else 
    select sum(get_hir_sal(empno)) 
    into v_child_sal
    from emp
    where mgr = p_empno;
    return(v_own_sal + v_child_sal);
  end if;  
end;
/

You call it with following query to get an identical result

select EMPNO, MGR, ENAME, SAL, 
  get_hir_sal(EMPNO) total
from emp
order by 1;

An efficient, pure SQL approach uses a connect by query to assign levels in the hierarchy, followed by a carefully constructed match_recognize operation. Once we have the nodes in the proper hierarchical order and with levels assigned, the descendants of any node can be recognized by a continuous sequence of nodes with levels strictly greater than the level of the starting node. match_recognize can identify such sequences efficiently, in a single pass over the rows.

This approach avoids the much more costly generation of multiple rows, followed by aggregation.

Possible downside: this will only work in Oracle 12.1 and higher - match_recognize does not exist in Oracle 11.2 and earlier. It is also supported only by a limited number of database products (including Oracle db), even though it is part of the SQL Standard, not a proprietary extension.

The solution looks something like this (using the standard scott.emp table):

with
  first_pass as (
    select  empno, ename, mgr, sal, rownum as ord, level as lvl
    from    scott.emp
    start   with mgr is null
    connect by mgr = prior empno
    order   siblings by ename     --  Not sure why you are doing it this way
  )
select empno, ename, mgr, sal, total_sal
from   first_pass
match_recognize (
  order    by ord
  measures x.empno as empno, x.ename as ename, x.mgr as mgr, x.sal as sal,
           sum(sal) as total_sal
  after match skip to next row
  pattern  ( x y* )
  define   y as lvl > x.lvl
);

Output:

     EMPNO ENAME             MGR        SAL  TOTAL_SAL
---------- ---------- ---------- ---------- ----------
      7839 KING                        5000      29025
      7698 BLAKE            7839       2850       9400
      7499 ALLEN            7698       1600       1600
      7900 JAMES            7698        950        950
      7654 MARTIN           7698       1250       1250
      7844 TURNER           7698       1500       1500
      7521 WARD             7698       1250       1250
      7782 CLARK            7839       2450       3750
      7934 MILLER           7782       1300       1300
      7566 JONES            7839       2975      10875
      7902 FORD             7566       3000       3800
      7369 SMITH            7902        800        800
      7788 SCOTT            7566       3000       4100
      7876 ADAMS            7788       1100       1100