Home » SQL & PL/SQL » SQL & PL/SQL » Accumulated Total of Sal according to hierarchy (connect by, analytic funcion)
Accumulated Total of Sal according to hierarchy (connect by, analytic funcion) [message #291408] Fri, 04 January 2008 02:08 Go to next message
immansoor2003
Messages: 3
Registered: March 2005
Junior Member
i want to get the following results, please help me if you can
thanks in advance

Level Employees Salary Acc.Sal total of sal
----- --------------- ------ ------- -------------------------
1 **KING 5000 29025 self+sub-ordinate
2 ****BLAKE 2850 9400 self+sub-ordinate
3 ******MARTIN 1250 1250 self total
3 ******ALLEN 1600 1600 self total
3 ******TURNER 1500 1500 self total
3 ******JAMES 950 950 self total
3 ******WARD 1250 1250 self total
2 ****CLARK 2450 3750 self+sub-ordinate
3 ******MILLER 1300 1300 self total
2 ****JONES 2975 10875 self+sub-ordinate
3 ******FORD 3000 3800 self+sub-ordinate
4 ********SMITH 800 800 self+sub-ordinate
3 ******SCOTT 3000 4100 self+sub-ordinate
4 ********ADAMS 1100 1100 self+sub-ordinate

Regards
Re: Accumulated Total of Sal according to hierarchy (connect by, analytic funcion) [message #291425 is a reply to message #291408] Fri, 04 January 2008 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you kow how to do the second column (and this is a standard connect by statement) then you know how to get all the subordinate starting from an employee.
If you know how to get these then you know how to get the salaries and the sum of all these gives you the fourth column.

Post what your tried.
Clue: there are 2 "connect by",one to get the second column, one to get the sum.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.


Regards
Michel
Re: Accumulated Total of Sal according to hierarchy (connect by, analytic funcion) [message #291451 is a reply to message #291425] Fri, 04 January 2008 04:44 Go to previous messageGo to next message
immansoor2003
Messages: 3
Registered: March 2005
Junior Member
I was trying following SQL many time, but may be i was doing a misktake, but when you told me, I tried it again, and got the results

thank you very much, but I want to also get this result with the help of "analytic functions", do you have another "clue" Smile


First Solution :
----------------
select level, lpad(ename, length(ename) + level * 2, '*') ename, sal,
(
select sum(sal) from emp
emp_inner connect by prior emp_inner.empno = emp_inner.mgr
start with emp_inner.empno = emp_outer.empno)
acc_sal_by_hierarchy
from emp emp_outer
connect by prior empno = mgr
start with mgr is null
Re: Accumulated Total of Sal according to hierarchy (connect by, analytic funcion) [message #291452 is a reply to message #291451] Fri, 04 January 2008 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand in
Michel Cadot wrote on Fri, 04 January 2008 10:07

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.


When you'll find a solution with analytic functions then try to find a solution with a screwdriver. Wink
Use analytics when only they are useful. It is not their purpose to go through a hierarchy.

Regards
Michel

Re: Accumulated Total of Sal according to hierarchy (connect by, analytic funcion) [message #291530 is a reply to message #291408] Fri, 04 January 2008 14:00 Go to previous message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
@immansoor2003
You can find several solutions of your problem in my blog:
http://volder-notes.blogspot.com/2007/12/reports-getting-total-of-all-children.html

[Updated on: Fri, 04 January 2008 14:02]

Report message to a moderator

Previous Topic: Incapsulation of instance variables in object type
Next Topic: trigger
Goto Forum:
  


Current Time: Tue Dec 06 02:26:17 CST 2016

Total time taken to generate the page: 0.13049 seconds