Post
Ask Question

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.
  1. CREATE TABLE ProductCategories(  
  2.     Id int IDENTITY(1,1) NOT NULL,  
  3.     Amount float NOT NULL,  
  4.     ProductCategoryId int NULL,  
  5.     Name VARCHAR(150) NULL)  
 Insert data into ProductCategories to populate the hierarchical structure.
  1. INSERT ProductCategories VALUES (100, NULL, N'A1')  
  2. INSERT ProductCategories VALUES (90, NULL, N'A2')  
  3. INSERT ProductCategories VALUES (80, NULL, N'A3')  
  4. INSERT ProductCategories VALUES (20, 1, N'A11')  
  5. INSERT ProductCategories VALUES (30, 1, N'A12')  
  6. INSERT ProductCategories VALUES (10, 1, N'A13')  
  7. INSERT ProductCategories VALUES (70, 2, N'A21')  
  8. INSERT ProductCategories VALUES (50, 2, N'A22')  
  9. INSERT ProductCategories VALUES (5, 4, N'A11.1')  
  10. INSERT ProductCategories VALUES (10, 4, N'A11.2')  
  11. INSERT ProductCategories VALUES (15, 5, N'A12.1')  
  12. INSERT ProductCategories VALUES (20, 5, N'A12.2')  
  13. INSERT ProductCategories VALUES (25, 9, N'A11.1.1')  
  14. INSERT ProductCategories VALUES (30, 9, N'A11.1.2')  
  15. INSERT ProductCategories VALUES (35, 10, N'A11.2.1')  
  16. 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.
  1. ;with C as  
  2. (  
  3.   select T.id,  
  4.          T.Amount,  
  5.          T.id as RootID  
  6.   from ProductCategories T  
  7.   union all  
  8.   select T.id,  
  9.          T.Amount,  
  10.          C.RootID  
  11.   from ProductCategories T  
  12.     inner join C   
  13.       on T.ProductCategoryId = C.id  
  14. )  
  15.   
  16. select T.id,  
  17.        T.ProductCategoryId,  
  18.        T.Name,  
  19.        T.Amount,  
  20.        S.AmountIncludingChildren  
  21. from ProductCategories T  
  22.   inner join (  
  23.              select RootID,  
  24.                     sum(Amount) as AmountIncludingChildren  
  25.              from C  
  26.              group by RootID  
  27.              ) as S  
  28.     on T.id = S.RootID  
  29. order by T.id  
  30. 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