father and son - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

father and son

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-10 : 07:59:59

i have this table:

id   pid    value
1    NULL    10
2    NULL    20
3    1       30
4    2       40
5    3       50
6    4       60

1 and 2 they are grandparenths
3 and 4 they are parents and childs of 1 and 2
and so on for 5 and 6

i want to see sum of value for roots of the tree. (for this example for 1 and 2)

1 is parent of 3 and 3 is parent of 5
2 is parent of 4 and 4 is parent of 6

i don't know what is hight of the tree

i mean

id  value 
1     80 = 30+50 = (id=3)+(id=50)
2     100 = 40+60 = (id=4)+(id=60)

how can i write this in sql

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-10 : 09:20:35

you don't want to sum the value of the root ?


declare @sample table
(
	id	int,
	pid	int,
	value	int
)
insert into @sample
select	1,    NULL,    10	union all
select	2,    NULL,    20	union all
select	3,    1,       30	union all
select	4,    2,       40	union all
select	5,    3,       50	union all
select	6,    4,       60

; with cte 
as
(
	-- anchor
	select	id, root = id, value = 0
	from	@sample s
	where	pid	is null

	union all

	select	s.id, root = c.root, s.value
	from	@sample s
		inner join cte c	on	s.pid	= c.id
)
select	root, sum(value)
from	cte
group by root

KH
[spoiler]Time is always against us[/spoiler]

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-10 : 10:04:17

oohhh.

it awesome, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources