Knowledgebase

In SQL Server a common table expression can be used to calculate totals / subtotals of a table containing hierarchical data:

-- Create test data:
if object_id('tempdb..#Hierarchy') is not null
begin
    drop table #Hierarchy
end

create table #Hierarchy (
    Id int not null,
    ParentId int null,
    Amount int not null
)

insert into #Hierarchy (Id, ParentId, Amount) values
    (1, null, 100)
,    (2, 1, 100)
,   (3, 1, 100)
,   (4, 1, 100)
,   (5, 4, 100)
,   (6, 4, 100)
,   (7, 4, 100)
,   (8, 7, 100)

;with CTE (ParentId, Id, Amount, IsParent)
as
(
    -- Add all records as "anchor", because foreach record we are going to calculate the total amount.
    select    h.ParentId
    ,        h.Id
    ,        h.Amount
    ,        0 as IsParent
    from    #Hierarchy h
    union all
    -- Recursively add parent records until root parent foreach record in "anchor".
    select    h.ParentId
    ,        h.Id
    ,        h.Amount
    ,        1 as IsParent
    from    CTE c
    join    #Hierarchy h on c.ParentId = h.Id
)

select        c1.Id
,            c1.ParentId
,            sum(c1.Amount) as TotalAmount
,            case when sum(c1.IsParent) > 0 then 1 else 0 end as IsParent
from        CTE c1
group by    c1.Id, c1.ParentId

Result