Recursive SUM Sql Server

Question

i need to do a recursive sum in SQL Server. I want a stored procedure where i can pass in a parent Id, then return a total for all the children(and children's children) linked to that parent id.

Here is what i have so far

IF object_id('tempdb..#Averages') IS NOT NULL
BEGIN
   DROP TABLE #Averages
END


CREATE TABLE #Averages
(
ID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
Name VARCHAR(255),
ParentID int,
Value INT
)

INSERT INTO #Averages(Name,ParentID,Value)VALUES('Fred',NULL,1)
INSERT INTO #Averages(Name,ParentID,Value)VALUES('Bets',NULL,1)

INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Wynand',ID,21 FROM #Averages WHERE      Name = 'Fred'  )

INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Dewald',ID,27 FROM #Averages WHERE     Name = 'Fred'  )
INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Katelynn',ID,1 FROM #Averages WHERE Name = 'Dewald'  )

INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Jacques',ID,28 FROM #Averages WHERE Name = 'Bets'  )
INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Luan',ID,4 FROM #Averages WHERE Name = 'Jacques'  )
INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Ruben',ID,2 FROM #Averages WHERE Name = 'Jacques'  )


;WITH Personal AS
(
SELECT N=1, ID,Name,ParentID,Value
FROM #Averages 
WHERE ParentID IS NULL
UNION ALL
SELECT N+1, Av.ID,Av.Name,Av.ParentID,Av.Value
FROM #Averages Av
INNER JOIN  Personal P ON P.ID = Av.ParentID
)

SELECT Name,
    SUM(Value) as Total
FROM Personal
WHERE N<=3
GROUP BY Name

Answer 1

Here's one way of achieving what you want, although it's slightly different to the approach you have above:

SQLFiddle

Create Table #Ancestors (
    ID int
  ,  Name VARCHAR(255)  
  ,  ParentID int
  ,  AncestryCompleteTF tinyint
  ,  Ancestors varchar(max)
  ,  TotalValue int    
)

INSERT INTO #Ancestors
SELECT
    ID
  , Name
  , ParentID
  , CASE ISNULL(ParentID, 0)
     WHEN 0 THEN 1
     ELSE 0
    END
  , CONVERT(VARCHAR, ISNULL(ParentID, ''))
  , Value
FROM
   Averages

WHILE EXISTS (SELECT * FROM #Ancestors WHERE AncestryCompleteTF = 0) 
BEGIN
  UPDATE C SET 
    C.Ancestors = P.Ancestors + ',' + CONVERT(VARCHAR, P.ID),
    C.AncestryCompleteTF = 1,
    C.TotalValue = P.TotalValue + C.TotalValue
  FROM #Ancestors C 
    INNER JOIN #Ancestors P ON (C.ParentID = P.ID) 
    AND P.AncestryCompleteTF = 1
END

SELECT 
  Name
, TotalValue 
FROM 
  #Ancestors

Basically I create a temporary table, and use a while loop to keep updating totals for rows where the parents have already been calculated (as this is just a case of adding the total for the current row to the parent row's total) until all the rows have been calculated. The rows where ParentID is null are set to being completed to begin with, so their direct descendants will be calculated first, and then the descendants of those rows etc. etc.

Answer 2

After playing around a bit i think i got it. I added a Top Level ID, which i set in the Root for the CTE. And then just add the top level id for all the recursion.

In the end i only sum, and basically use TopLevelId to join to the Top level Table.

IF object_id('tempdb..#Averages') IS NOT NULL
BEGIN
   DROP TABLE #Averages
END

CREATE TABLE #Averages
(
    ID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    Name VARCHAR(255),
    ParentID int,
    Value INT
)

 INSERT INTO #Averages(Name,ParentID,Value)VALUES('Fred',NULL,1)
 INSERT INTO #Averages(Name,ParentID,Value)VALUES('Bets',NULL,1)

 INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Wynand',ID,21 FROM #Averages WHERE Name = 'Fred'  )

 INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Dewald',ID,27 FROM #Averages WHERE Name = 'Fred'  )
 INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Katelynn',ID,1 FROM #Averages WHERE Name = 'Dewald'  )

 INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Jacques',ID,28 FROM #Averages WHERE Name = 'Bets'  )
 INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Luan',ID,4 FROM #Averages WHERE Name = 'Jacques'  )
 INSERT INTO #Averages(Name,ParentID,Value)(SELECT 'Ruben',ID,2 FROM #Averages WHERE Name = 'Jacques'  )


;WITH Personal AS
(
    SELECT N=1, 
        ID,
        Name,
        ParentID,
        Value,
        TopLevelID =ID
    FROM #Averages 
    WHERE ParentID IS NULL

    UNION ALL

    SELECT N+1, 
        Av.ID,
        Av.Name,
        Av.ParentID,
        Av.Value,
        TopLevelID =P.TopLevelID
    FROM #Averages Av
    INNER JOIN  Personal P ON P.ID = Av.ParentID
)

SELECT SUM(P.Value) AS Total,
        A.Name
FROM Personal P
INNER JOIN #Averages A on A.ID = P.TopLevelID
GROUP BY A.Name