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