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

Question

I have a Oracle hiërarchical query using a WITH clause (common table expression). My goal is to calculate the sum of the salaries of the current row and the child rows. I've come up with the following, using a correlated subquery between a cte that calculates the sum of every row of which the current name is part of one of the paths of the previous cte. Is there a better way to do this?

with managers(empno, mgr, ename, sal, path) as (
  select empno, mgr, ename, sal, '/'||empno
  from emp
  where mgr is null
  union all
  select e.empno, e.mgr, e.ename, e.sal, m.path||'/'||e.empno
  from emp e join managers m
  on (e.mgr = m.empno)
)
search depth first by ename set search_order
, totals(empno, total) as (
  select e.empno, (select sum(sal)
                   from managers
                   where regexp_like( path||'/', '/'||e.empno||'/'))
  from emp e)
select empno, mgr, ename, sal, path, total
from managers join totals using (empno)
order by search_order;

Result:

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

Answer 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

Answer 2

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;