Hierarchical Query: Roll-up leaf nodes to ancestors.

This query may be more applicable to a bill of materials type of query. I’ve added a new column to the EMP table (value number), and populated it with 1. This query the sums up these leaf node values to their manager, and to the manager’s manager.

Setup: Add a values column with

alter table emp add (value number default 1);

The query doing the rolling up:

with totals
     as (select empno,
                ename,
                mgr,
                value,
                level                                     lev,
                rownum                                    rn,
                sum(Nvl(value, 0))
                  over(
                    partition by Connect_by_root(empno) ) tot_val
         from   emp t
         connect by prior empno = mgr),
     lev1
     as (select empno,
                mgr,
                rn,
                ename,
                value,
                tot_val
         from   totals
         where  lev = 1)
select Lpad(' ', 2 * level, ' ')
       ||ename name,
       value,
       tot_val
from   lev1
connect by prior empno = mgr
start with mgr is null