Recursive sum in tree structure

Question

I have a tree struture in a single table. The table is a tree of categories that can be nested endlessly. Each category has a ProductCount column that tells how many products are directly in the category (not summing child categories).

Id  | ParentId | Name      | ProductCount ------------------------------------ 1   | -1       | Cars      | 0 2   | -1       | Bikes     | 1 3   | 1        | Ford      | 10 4   | 3        | Mustang   | 7 5   | 3        | Focus     | 4 

I would like to make a sql query that for each row/category gives me the number of products including the ones in the child categories.

The output for the table above should be

Id  | ParentId | Name      | ProductCount | ProductCountIncludingChildren -------------------------------------------------------------------------- 1   | -1       | Cars      | 0            | 21 2   | -1       | Bikes     | 1            | 1 3   | 1        | Ford      | 10           | 21 4   | 3        | Mustang   | 7            | 7 5   | 3        | Focus     | 4            | 4 

I know I probably should use CTE, but cant quite get it working the way it should.

Any help is appreciated!

Answer 1

You can use a recursive CTE where you in the anchor part get all rows and in the recursive part join to get the child rows. Remember the original Id aliased RootID from the anchor part and do sum aggregate in the main query grouped by RootID.

SQL Fiddle

MS SQL Server 2012 Schema Setup:

create table T (   Id int primary key,   ParentId int,   Name varchar(10),   ProductCount int );  insert into T values (1, -1, 'Cars',    0), (2, -1, 'Bikes',   1), (3,  1, 'Ford',    10), (4,  3, 'Mustang', 7), (5,  3, 'Focus',   4);  create index IX_T_ParentID on T(ParentID) include(ProductCount, Id); 

Query 1:

with C as (   select T.Id,          T.ProductCount,          T.Id as RootID   from T   union all   select T.Id,          T.ProductCount,          C.RootID   from T     inner join C        on T.ParentId = C.Id ) select T.Id,        T.ParentId,        T.Name,        T.ProductCount,        S.ProductCountIncludingChildren from T   inner join (              select RootID,                     sum(ProductCount) as ProductCountIncludingChildren              from C              group by RootID              ) as S     on T.Id = S.RootID order by T.Id option (maxrecursion 0) 

Results:

| ID | PARENTID |    NAME | PRODUCTCOUNT | PRODUCTCOUNTINCLUDINGCHILDREN | |----|----------|---------|--------------|-------------------------------| |  1 |       -1 |    Cars |            0 |                            21 | |  2 |       -1 |   Bikes |            1 |                             1 | |  3 |        1 |    Ford |           10 |                            21 | |  4 |        3 | Mustang |            7 |                             7 | |  5 |        3 |   Focus |            4 |                             4 |