Sum a column for a set of Trees on a Recursive Table

• joshua.wallace

  SSC Enthusiast

  Points: 194

  More actions

  March 16, 2012 at 5:02 am

  #265845

  Hi...

  I have a table (fctTable), which references another table (refTable) that has a parent/child relationship to it's self.

  What i need to do is for each row in the fctTable i need to get the sum of a column on the refTable for whole tree of parents.

  this is hard to explain so i have an example...

  Here are my two tables:

  fctTable

  Id | refTableId

  1 | 2

  2 | 7

  3 | 4

  4 | 9

  5 | 8

  refTable

  Id | ParentId | Value

  1 | 0 | 17

  2 | 1 | 11

  3 | 0 | 23

  4 | 5 | 5

  5 | 3 | 7

  7 | 2 | 13

  8 | 0 | 3

  9 | 8 | 19

  and i need to get this out:

  Result

  fctTableId | (refTableIds) | SUM(refTable.Value) |

  1 | 2,1 | 11 + 17 = 28

  2 | 7,2,1 | 13 + 11 + 17 = 41

  3 | 4,5,3 | 5 + 7 + 23 = 35

  4 | 9,8 | 19 + 3 = 22

  5 | 8 | 3 = 3

  I don't need the refTableId's column in the results, thats just to help me work out what the heck i'm doing....

  I can do it by creating a table valued function similar to the SP mentioned by Anish M here, which gets the tree for a given refTableId, and then calling that from a select statment that chooses the rows in the fctTable i want to use.

  but that seems clunky and slow (i may have millions of records in the fctTable to do this for so slow ≡ bad)

  I was wondering if any one can think of a better solution... any ideas?

• Mark Cowne

  One Orange Chip

  Points: 26979

  More actions

  March 16, 2012 at 5:50 am

  #1460438

  You can use recursive CTEs for this

  SET NOCOUNT ON

  DECLARE @fctTable TABLE(Id INT,refTableId INT)

  INSERT INTO @fctTable(Id,refTableId)

  SELECT 1 , 2 UNION ALL

  SELECT 2 , 7 UNION ALL

  SELECT 3 , 4 UNION ALL

  SELECT 4 , 9 UNION ALL

  SELECT 5 , 8;

  DECLARE @refTable TABLE(Id INT, ParentId INT, Value INT)

  INSERT INTO @refTable(Id, ParentId, Value)

  SELECT 1 , 0 , 17 UNION ALL

  SELECT 2 , 1 , 11 UNION ALL

  SELECT 3 , 0 , 23 UNION ALL

  SELECT 4 , 5 , 5 UNION ALL

  SELECT 5 , 3 , 7 UNION ALL

  SELECT 7 , 2 , 13 UNION ALL

  SELECT 8 , 0 , 3 UNION ALL

  SELECT 9 , 8 , 19;

  WITH Recur AS (

  SELECT f.Id AS fctTableId,

  r.Id AS refTableId,

  r.Id,

  r.ParentId,

  r.Value AS SumValue

  FROM @refTable r

  INNER JOIN @fctTable f ON f.refTableId=r.Id

  UNION ALL

  SELECT r.fctTableId,

  r.refTableId,

  t.Id,

  t.ParentId,

  t.Value + r.SumValue

  FROM @refTable t

  INNER JOIN Recur r ON r.ParentId=t.Id

  )

  SELECT fctTableId,

  SumValue

  FROM Recur

  WHERE ParentId=0

  ORDER BY fctTableId;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• joshua.wallace

  SSC Enthusiast

  Points: 194

  More actions

  March 16, 2012 at 6:06 am

  #1460439

  BOOM! Job done.

  Thanks Mark!

  (now. how does that work...)

• Jeff Moden

  SSC Guru

  Points: 1004593

  More actions

  March 16, 2012 at 6:35 am

  #1460452

  Oh... be careful, now. That only works if each top level item only has a straight trunk instead of a tree. Run the code with the following data and see what I mean. I added a node to ParentID = 3.

  SET NOCOUNT ON

  DECLARE @fctTable TABLE(Id INT,refTableId INT)

  INSERT INTO @fctTable(Id,refTableId)

  SELECT 1 , 2 UNION ALL

  SELECT 2 , 7 UNION ALL

  SELECT 3 , 4 UNION ALL

  SELECT 4 , 9 UNION ALL

  SELECT 5 , 8;

  DECLARE @refTable TABLE(Id INT, ParentId INT, Value INT)

  INSERT INTO @refTable(Id, ParentId, Value)

  SELECT 1 , 0 , 17 UNION ALL

  SELECT 2 , 1 , 11 UNION ALL

  SELECT 3 , 0 , 23 UNION ALL

  SELECT 4 , 5 , 5 UNION ALL

  SELECT 5 , 3 , 7 UNION ALL

  SELECT 7 , 2 , 13 UNION ALL

  SELECT 8 , 0 , 3 UNION ALL

  SELECT 9 , 8 , 19 UNION ALL

  SELECT 10, 3 , 100 --Added

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• joshua.wallace

  SSC Enthusiast

  Points: 194

  More actions

  March 16, 2012 at 8:03 am

  #1460521

  oh yes, good spot jeff, but that is my fault for not being clear; I probably shouldn't have said "whole".

  What I really needed to sum is the route from root to leaf...

  The tree whose root is refTableId = 3 now looks something like this:

  . .4

  . . .10 . 5

  .\ . /

  . .3

  so for refTableId = 5 i want the sum for the refTable records 5+3 (i.e. 7 + 23 = 30)

  if it's 10 i would expect 10+3 (100 + 23 = 123)

  and 4 would yeild 4+5+3 (5 + 7 + 23 = 35)

  Mark's solution to gives me exactly that (whether by luck or design we shall have to leave for him to decide!)

  Adding these records to fctTable:

  SELECT 6 , 10 UNION ALL

  SELECT 7 , 5;

  i get:

  128

  241

  335

  422

  53

  6123

  730

  as expected (note the results for fctTableId 3, 6 and 7).

  As an aside, Atif-ullah posted an alternate solution to my question on the microsoft forum...