Need a Query to sumup the values according to Department structure

• mohanaraj

  SSC-Addicted

  Points: 406

  More actions

  September 16, 2010 at 5:12 am

  #387800

  Hai,

  I want to sum up the child department values and display in the parent record

  Below is the Table.

  DepartmentId ParentId DeptName Incoming

  1 ------------0------ PhnDeprt ------14.00

  3 ------------1-------Facilities ------01.00

  2 ------------0-------Calse ---------- 5.00

  4 ------------2-------CalFacitity ------1.00

  5 ----------- 4 -------CalFF -----------2.00

  I want the result as

  DepartmentId ParentId DeptName Incoming

  1 ------------0------ PhnDeprt ------15.00

  3 ------------1-------Facilities ------01.00

  2 ------------0-------Calse ---------- 8.00

  4 ------------2-------CalFacitity ------3.00

  5 ----------- 4 -------CalFF -----------2.00

  Here 'PhnDeprt' department have child 'Facilities' so in the result 1.00 + 14.00 = 15.00 this result will be displayed in the Parent record i.e. in the 'PhnDeprt''s Incoming column

  I tried WITH but how can I use Aggregate function in WITH for sumup ?

  Please advise and help me

  Thanks in advance.

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  September 16, 2010 at 5:18 am

  #1222072

  Should be fairly straight forward if you use a recursive CTE.

  Some similar to this ...

  http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/05/27/challenge-19-an-explanation-of-a-query.aspx

  ---

  
  Clear Sky SQL
  My Blog[/url]