Home » SQL & PL/SQL » SQL & PL/SQL » Sum through a hierarchy
Sum through a hierarchy [message #256532] Sun, 05 August 2007 11:29 Go to next message
sivanesh
Messages: 1
Registered: August 2007
Junior Member
helo,
i have one question here..

2 input tables:
Employee table
EID DESG REPORT_TO_EID
1 Asso 4
2 Asso 4
3 Asso 4
4 PM 5
5 RM NULL

Client Table:
EID CID
1 25
1 35
1 45
2 55
2 65
3 75
4 95

and OUTPUT should be like this....
EID No_of_Clients
1 3
2 2
3 1
4 7
5 7

assume desg hierarchy as Tree format
EID-5 is topmost , next comes eid-4, below that eid-1,2,3
so EID has control of 7 clients, 4 has control of 7 clients
and EID 1,2,3 has 3,2,1 cients respectively....

please tell me the Select Query to get this output....

[Edit: change meaningless title]

[Updated on: Sun, 05 August 2007 11:55] by Moderator

Report message to a moderator

Re: challenging question...please tell me..... [message #256533 is a reply to message #256532] Sun, 05 August 2007 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, you have to read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then if you read these stickies, you'd search on AskTom and find there is a whole thread on how to sum over a hierarchy.

Regards
Michel
Re: challenging question...please tell me..... [message #256534 is a reply to message #256532] Sun, 05 August 2007 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I am in a good mood I give you the link:
Hierarchial query with cumulative factor

Next time, use a meaningful title instead of this silly "challenging question...please tell me....."

Regards
Michel
Re: Sum through a hierarchy [message #256645 is a reply to message #256532] Mon, 06 August 2007 04:10 Go to previous messageGo to next message
charansundaram
Messages: 8
Registered: August 2007
Location: INDIA
Junior Member
hi,

Try this query

To total count
----------------

select EID , count(CID)
from Client_Table
group by EID;


To total sum
-------------

select EID , sum(CID)
from Client_Table
group by EID;


Re: Sum through a hierarchy [message #256659 is a reply to message #256645] Mon, 06 August 2007 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And this is wrong.
Where is the reference to the hierarchy?
How could you expect to get the result asked by the OP.
Please double check before posting.

Regards
Michel
Re: Sum through a hierarchy [message #257722 is a reply to message #256659] Thu, 09 August 2007 01:34 Go to previous messageGo to next message
charansundaram
Messages: 8
Registered: August 2007
Location: INDIA
Junior Member


hai,

his question
==============

Client Table:
EID CID
1 25
1 35
1 45
2 55
2 65
3 75
4 95

and OUTPUT should be like this....
EID No_of_Clients
1 3
2 2
3 1
4 7
5 7




My answer
==========


To total count
----------------

select EID , count(CID)
from Client_Table
group by EID;



I have give sum option for addional results. not solution for this one.

Correct me if i have given anything wrong.
Re: Sum through a hierarchy [message #257748 is a reply to message #257722] Thu, 09 August 2007 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have give sum option for addional results. not solution for this one.

Did you say you answered a question that is not asked but not the one that was posted?

Regards
Michel
icon10.gif  Re: challenging question...please tell me..... [message #260325 is a reply to message #256533] Sat, 18 August 2007 08:56 Go to previous message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

Michel Cadot wrote on Sun, 05 August 2007 19:48
First, you have to read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then if you read these stickies, you'd search on AskTom and find there is a whole thread on how to sum over a hierarchy.

Regards
Michel


he right in this
Previous Topic: ORA-12801
Next Topic: Insert rows with no duplicate
Goto Forum:
  


Current Time: Sat Dec 03 03:54:54 CST 2016

Total time taken to generate the page: 0.08518 seconds