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

  • 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?

  • 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
  • BOOM! Job done.

    Thanks Mark!

    (now. how does that work...)

  • 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)

  • 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...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply