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 ONDECLARE @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