Hierarchical Cumulative Sum Using SQL Server

Recently, I had a complicated scenario while creating reports. I need to have each level, which may be the sum of all children (in the hierarchy) in addition to any values, which are set against that value itself for the amount column. I failed to produce the expected results, using Linq. Thanks to SQL server, for coming to the rescue. Using CTE, it's easy to produce what exact result I want. In this blog, I am going to share, how to do cumulative sum on a hierarchical level, using SQL Server.

 

To discuss the scenario, let's create a table in SQL Server.

CREATE TABLE ProductCategories(
    Id int IDENTITY(1,1) NOT NULL,
    Amount float NOT NULL,
    ProductCategoryId int NULL,
    Name VARCHAR(150) NULL)

 Insert data into ProductCategories to populate the hierarchical structure.

INSERT ProductCategories VALUES (100, NULL, N'A1')
INSERT ProductCategories VALUES (90, NULL, N'A2')
INSERT ProductCategories VALUES (80, NULL, N'A3')
INSERT ProductCategories VALUES (20, 1, N'A11')
INSERT ProductCategories VALUES (30, 1, N'A12')
INSERT ProductCategories VALUES (10, 1, N'A13')
INSERT ProductCategories VALUES (70, 2, N'A21')
INSERT ProductCategories VALUES (50, 2, N'A22')
INSERT ProductCategories VALUES (5, 4, N'A11.1')
INSERT ProductCategories VALUES (10, 4, N'A11.2')
INSERT ProductCategories VALUES (15, 5, N'A12.1')
INSERT ProductCategories VALUES (20, 5, N'A12.2')
INSERT ProductCategories VALUES (25, 9, N'A11.1.1')
INSERT ProductCategories VALUES (30, 9, N'A11.1.2')
INSERT ProductCategories VALUES (35, 10, N'A11.2.1')
INSERT ProductCategories VALUES (40, 10, N'A11.2.2')

The hierarchy will look, as shown below.

 

Image 1:Parent Child Hierarchical structure

Query

Using CTE (Common Table Expressions), we will first flatten the hierarchical data. Afterwards, we will use inner join ProductCategories table CTE generated table.

;with C as
(
  select T.id,
         T.Amount,
         T.id as RootID
  from ProductCategories T
  union all
  select T.id,
         T.Amount,
         C.RootID
  from ProductCategories T
    inner join C
      on T.ProductCategoryId = C.id
)

select T.id,
       T.ProductCategoryId,
       T.Name,
       T.Amount,
       S.AmountIncludingChildren
from ProductCategories T
  inner join (
             select RootID,
                    sum(Amount) as AmountIncludingChildren
             from C
             group by RootID
             ) as S
    on T.id = S.RootID
order by T.id
option (maxrecursion 0)

Result

 

Image 2: Query result 

 

After running this query, you will get the cumulative sum of the child amount at each node.

 

I hope, like me, you got your solution, using this query.

 

Reference

• Recursive sum in tree structure
• Using Common Table Expressions
• WITH common_table_expression (Transact-SQL)