CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
cuongle
cuongle

Reputation: 75306

How to SUM all descendants value of every node on a tree table

Assume I have a Category table (tree format) like below:

enter image description here

I would like to SUM all descendants value of every node on the table and the expected result like highlighted column as below:

enter image description here

the sum_value of Cat 1 = Cat 1 value + Cat 1.1 value + Cat 1.2 value

how to write effective SQL to get the result on every level and node?

Thanks

Upvotes: 0

Views: 298

Answers (1)

eshi
eshi

Reputation: 71

I found the solution to this on this tread: Recursive sum in tree structure

Here's one of the solutions modified for you table. There's lots of discussion on that thread on how it works.

with cte as
(
  select a.id, a.value, a.id as rootid
  from a
  union all
  select a.id, a.value, cte.rootid
  from a
  inner join cte
  on a.parentid=cte.id
)
select a.*, s.sum_value
  from a
  inner join (
    select rootid, sum(value) as sum_value
    from cte
    group by rootid
    ) as s
  on a.Id = S.RootID
order by a.Id

Here's a fiddle link for it: sqlfiddle

Upvotes: 2

Related Questions