Friday, 27 September 2013

Aggregate Self-Referencing Table

Aggregate Self-Referencing Table

I've seen several questions/answers on how to recursively query a
self-referencing table, but I am struggling to apply the answers I've
found to aggregate up to each parent, grandparent, etc. regardless of
where the item sits in the hierarchy.
MyTable
-----------
Id
Amount
ParentId
Data:
Id Amount Parent Id
1 100 NULL
2 50 1
3 50 1
4 25 2
5 10 4
If I were to run this query without filtering, and SUMming amount, the
result would be:
Id SumAmount
1 235
2 85
3 50
4 35
5 10
In other words, I want to see each item in MyTable and it's total Amount
with all children.

No comments:

Post a Comment