July 8, 2008 at 4:37 am
Hi
I have a table as follows
ID ParentID Amount
5 0
6 5 0
7 6 15
8 6 8
9 6 50
10 6 0
11 10 20
12 10 54
13 10 52
14 10 1
15 10 89
16 10 20
17 5 11
The 'ID' is the Primary key of the table. 'ParentID' is the foreign key referencing 'ID'. It shows its parent. The Instances with ID's 5, 6 and 10 are '0' because the are determined by thier 'children. That is the ammount of instance with id 10 should be the sum of instance id's 11, 12, 13, 14, 15 and 16 amounts. And Amount for id 6 is the sum of amounts of instace id's 10, 9, 8 and 7 etc.
I am wondering if anyone knows of a way i can do this
Thankyou for the help
July 8, 2008 at 3:21 pm
if your heirarchy is just 2 levels (parent, child), then just group by ParentId.
select ParentId, sum(Amount) as total
from {table}
group by ParentId
if your heirarchy is open-ended, you'll need to use a CTE. search the forums here and look in books online for CTE for some specific examples.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply