Aggregation within a CTE

  • Hello,

    I'm having quite a hard time writing a recursive CTE for summing values (standard ones are ok). The point I'm struggling with is when summing a group, if one row of that group has some child that parent AvCost2 value must be replaced with the sum of it's child and so on. If a child of that parent has some child we must sum the lowers child first, push the sum one level higher, redo the sum push the new value up to the parent then sum all the parent for the final value. (There will be more level then those describe here hence the appealing recursion)

    Here's a demo:

    The result set give the good value when ParentID IS NULL but does not sum the values from its child.

    The expected result would be:

    Total of 1.234234

    If Total is 2.23 and some digits then the parent HeaderID = 51 AvCost2 value was used instead of the sum of it's child.

    The Final sum should be 0.194131 + 0.179581 + 0.860522 (all parents AvCost2 without child + the sums of the child replacing the AvCost2 of that parent with the sum value)

    DECLARE @Table TABLE (

    HeaderID INT NOT NULL

    , InQty DECIMAL (18,6)

    , UsQty DECIMAL (18,6)

    , AvCost DECIMAL (18,6)

    , AvCost2 DECIMAL (18,6)

    , Qty DECIMAL (18,6)

    , ParentID INT

    , ChildID INT

    )

    INSERT INTO @Table VALUES (49, 1.0, 350.0, 13.59, 0.194131, 5.0, NULL, NULL)

    INSERT INTO @Table VALUES (51, 1.0, 1000.0, 107.569, 1.860552, 8.0, NULL, 10)

    INSERT INTO @Table VALUES (34, 1.0, 350.0, 17.99, 3.747917, 10.0, 10, NULL)

    INSERT INTO @Table VALUES (37, 1.0, 1000.0, 5.25, 0.010500, 20.0, 10, NULL)

    INSERT INTO @Table VALUES (36, 1.0, 75.0, 10.0, 2.666667, 20.0, 10, NULL)

    INSERT INTO @Table VALUES (35, 1.0, 75.0, 12.95, 3.453333, 20.0, 10, NULL)

    INSERT INTO @Table VALUES (32, 1.0, 17.0, 28.99, 53.290441, 31.25, 10, NULL)

    INSERT INTO @Table VALUES (50, 2.21, 16.0, 6.35, 0.179581, 1.0, NULL, NULL)

    INSERT INTO @Table VALUES (33, 1.0, 16.0, 3.14, 44.400452, 500.0, 10, NULL)

    SELECT * FROM @Table

    ;WITH GetCost (ParentID, AvCost2) AS

    (

    SELECT

    ParentID

    , SUM (CASE

    WHEN ChildID IS NULL THEN AvCost2

    ELSE 0

    END

    ) AS 'AvCost2'

    FROM

    @Table

    WHERE

    ParentID IS NULL

    GROUP BY ParentID

    UNION ALL

    SELECT

    itmCost.ParentID

    , itmCost.AvCost2 / tt.UsQty / tt.InQty * tt.Qty AS 'AvCost2'

    FROM

    @Table tt

    INNER JOIN GetCost itmCost

    ON tt.ChildID = itmCost.ParentID

    )

    SELECT * FROM GetCost

    Thanks

  • I've coded an iterative replacement but it lack the optimization of the CTE. Still if someone could get the CTE to work I would be very grateful.

    Here's the working example of what I'm looking for:

    (the LevelDepth column was added from the previous example only)

    DECLARE @Table TABLE (

    HeaderID INT NOT NULL

    , InQty DECIMAL (18,6)

    , UsQty DECIMAL (18,6)

    , AvCost DECIMAL (18,6)

    , AvCost2 DECIMAL (18,6)

    , Qty DECIMAL (18,6)

    , ParentID INT

    , ChildID INT

    , LevelDepth INT

    )

    INSERT INTO @Table VALUES (49, 1.0, 350.0, 13.59, 0.194131, 5.0, NULL, NULL, 1)

    INSERT INTO @Table VALUES (51, 1.0, 1000.0, 107.569, 1.860552, 8.0, NULL, 10, 1)

    INSERT INTO @Table VALUES (34, 1.0, 350.0, 17.99, 3.747917, 10.0, 10, NULL, 2)

    INSERT INTO @Table VALUES (37, 1.0, 1000.0, 5.25, 0.010500, 20.0, 10, NULL, 2)

    INSERT INTO @Table VALUES (36, 1.0, 75.0, 10.0, 2.666667, 20.0, 10, NULL, 2)

    INSERT INTO @Table VALUES (35, 1.0, 75.0, 12.95, 3.453333, 20.0, 10, NULL, 2)

    INSERT INTO @Table VALUES (32, 1.0, 17.0, 28.99, 53.290441, 31.25, 10, NULL, 2)

    INSERT INTO @Table VALUES (50, 2.21, 16.0, 6.35, 0.179581, 1.0, NULL, NULL, 1)

    INSERT INTO @Table VALUES (33, 1.0, 16.0, 3.14, 44.400452, 500.0, 10, NULL, 2)

    --SELECT * FROM @Table

    DECLARE @i AS INT = 0

    DECLARE @MaxLoop AS INT

    SELECT @MaxLoop = MAX(LevelDepth) FROM @Table

    WHILE @MaxLoop >= 1

    BEGIN

    --SELECT ParentID, SUM(AvCost2) AS 'TheTotal' FROM @Table WHERE LevelDepth = @MaxLoop GROUP BY ParentID

    UPDATE @Table SET AvCost2 = ChildSum.TheTotal / t.InQty / t.UsQty * t.Qty

    FROM @Table t

    INNER JOIN (SELECT ParentID, SUM(AvCost2) AS 'TheTotal' FROM @Table WHERE LevelDepth = @MaxLoop GROUP BY ParentID) ChildSum

    ONt.ChildID = ChildSum.ParentID

    SET @MaxLoop-=1

    END

    SELECT SUM(AvCost2) FROM @Table WHERE LevelDepth = 1

  • This is one of the way you can do it.

    DECLARE @Table TABLE (

    HeaderID INT NOT NULL

    , InQty DECIMAL (18,6)

    , UsQty DECIMAL (18,6)

    , AvCost DECIMAL (18,6)

    , AvCost2 DECIMAL (18,6)

    , Qty DECIMAL (18,6)

    , ParentID INT

    , ChildID INT

    )

    INSERT INTO @Table VALUES (49, 1.0, 350.0, 13.59, 0.194131, 5.0, NULL, NULL)

    INSERT INTO @Table VALUES (51, 1.0, 1000.0, 107.569, 1.860552, 8.0, NULL, 10)

    INSERT INTO @Table VALUES (34, 1.0, 350.0, 17.99, 3.747917, 10.0, 10, NULL)

    INSERT INTO @Table VALUES (37, 1.0, 1000.0, 5.25, 0.010500, 20.0, 10, NULL)

    INSERT INTO @Table VALUES (36, 1.0, 75.0, 10.0, 2.666667, 20.0, 10, NULL)

    INSERT INTO @Table VALUES (35, 1.0, 75.0, 12.95, 3.453333, 20.0, 10, NULL)

    INSERT INTO @Table VALUES (32, 1.0, 17.0, 28.99, 53.290441, 31.25, 10, NULL)

    INSERT INTO @Table VALUES (50, 2.21, 16.0, 6.35, 0.179581, 1.0, NULL, NULL)

    INSERT INTO @Table VALUES (33, 1.0, 16.0, 3.14, 44.400452, 500.0, 10, NULL)

    SELECT * FROM @Table

    ;

    WITH GetCost (ParentID, AvCost2,ChildID) AS

    (

    SELECT

    ParentID,

    SUM (AvCost2) AS 'AvCost2',

    ChildID

    FROM

    @Table

    GROUP BY ParentID,ChildID

    UNION ALL

    SELECT

    tt.ParentID,

    CAST(itmCost.AvCost2 / tt.InQty / tt.UsQty * tt.Qty as DECIMAL(38,6)) ,

    itmCost.ChildID

    FROM

    @Table tt

    INNER JOIN GetCost itmCost

    ON tt.ChildID = itmCost.ParentID

    )

    SELECT SUM(AvCost2) FROM GetCost

    WHERE ChildID IS NULL and ParentID IS NULL

  • Thank you for taking time! I'll take a look at the solution and understand what I did wrong with mine.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply