Topic: SQL*Plus and PL/SQL >> How to get Cummulative Subtotals and Hierarchial Relationship in One Query
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: How to get Cummulative Subtotals and Hierarchial Relationship in One Query |
| dksampat |
Posted: Mar 09, 2007 02:47:37 AM |
Total Post: 7
Joined: Jan, 2007
|
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
|
|
|
 bunty |
| Posted: Mar 14, 2007 07:56:42 AM |  |
|
Total Post: 6
Joined: Mar, 2007
|
Hye.
please try this..........
SELECT T1.ID,
T1.VALUE,SUM(T1.VALUE)OVER(ORDER BY T1.ID,T1.VALUE) CNT
FROM T1,T3 WHERE T1.ID = T3.ID
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 843 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 272 |
| neeraj sharma | 252 |
| Ramesh Jois | 245 |
| Bishal Khetan | 207 |
|
|
