How to get Cummulative Subtotals and Hierarchial Relationship in One Query

[dksampat]

Hi All,

I need to display rows in a hierarchial manner along with Cummulative Subtotals. But when i use the below query, I am not getting the Cummulative subtotals properly, however the hierarchy is properly maintainted. See below for the query, sample outputs and a reproducible testcase.


create table t1 (id varchar2(100), value number);
create table t3 (id varchar2(100), supervisor_id varchar2(100));
insert into t1 values('Manager1',10);
insert into t1 values('Superviosr1',5);
insert into t1 values('Superviosr2',6);
insert into t1 values('User1',1);
insert into t1 values('User2',2);
insert into t1 values('User3',3);
insert into t3 values('Manager1','Director1');
insert into t3 values('Superviosr1','Manager1');
insert into t3 values('Superviosr2','Manager1');
insert into t3 values('User1','Superviosr1');
insert into t3 values('User2','Superviosr2');
insert into t3 values('User3','Superviosr2');

select * from t1

ID VALUE
-------------------
Manager1 10
Superviosr1 5
Superviosr2 6
User1 1
User2 2
User3 3


SELECT LPAD(ID,LENGTH(ID) + 3*(LEVEL-1),'**') hir, value
FROM
(select distinct t1.id id,t3.supervisor_id sid , sum (value) value
from T1 , T3
WHERE T1.ID = T3.ID
group by t1.id,t3.supervisor_id
)
start with SID='Director1'
connect by prior ID = SID;

Current Output
**************
Output that I am getting because of the above query is :

HIR VALUE
---------------------
Manager1 10
***Superviosr1 5
******User1 1
***Superviosr2 6
******User2 2
******User3 3

My Desired Output
*****************
Though the hierarchy is properly maintainted, I am not getting Cummulative Subtotals here.
The desired output that I want is :

HIR VALUE
---------------------
Manager1 27
***Superviosr1 6
******User1 1
***Superviosr2 11
******User2 2
******User3 3

where Value(Supervisor1) = Value(Supervisor1) + Value(User1) = 5 + 1 = 6 (See t1 table for value list)
Value(Superviosr2) = Value(Superviosr2) + Value(User2) + + Value(User3) = 6 + 2 + 3 = 11
Value(Manager1) = Value(Manager1) + Value(Supervisor1) + Value(Superviosr2) = 10 + 6 + 11 = 27

Kindly please help me in the same.

Regards,
Dinesh