Tree structure recursive sum of aggregated values from another table

Question

I am trying to do a recursive sum using CTE but the value that I need have to be aggregated from another table.

The sum of the child is summed up and added to the value of the parent and my problem is that group by and aggregation is not allowed in the recursive part. There might be another approach for this scenario.

My data:

Main table (tEvent)

Id                   ParentId             Name
-------------------------------------------------------
1                                         Main Project
2                    1                      Sub Project
3                    2                        Task 1.1
4                    2                        Task 1.2
5                    1                      Task 2
6                    1                      Task 3

Extra table (tBooking)

Id                   EventId              Value
------------------------------------------------
1                    1                    4
2                    2                    5
3                    2                    10
4                    3                    8
5                    4                    5
6                    4                    15
7                    5                    18
8                    6                    40
9                    6                    12
10                   6                    9

Expected result

Id                   Name                 SummedUp
---------------------------------------------------
1                    Main Project         126
2                      Sub Project        43
3                        Task 1.1         8
4                        Task 1.2         20
5                      Task 2             18
6                      Task 3             61

Schema setup:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_NAME] = 'tEvent')
BEGIN
    DROP TABLE [tEvent]
END

CREATE TABLE [dbo].[tEvent]  
(
    [Id] NVARCHAR (50) NOT NULL, 
    [Name] NVARCHAR (50) NOT NULL,
    [ParentId] NVARCHAR (50) NOT NULL
);
    
INSERT INTO tEvent(Id, ParentId, Name) VALUES ('1','','Main Project');
INSERT INTO tEvent(Id, ParentId, Name) VALUES ('2','1','Sub Project');
INSERT INTO tEvent(Id, ParentId, Name) VALUES ('3','2','Task 1.1');
INSERT INTO tEvent(Id, ParentId, Name) VALUES ('4','2','Task 1.2');
INSERT INTO tEvent(Id, ParentId, Name) VALUES ('5','1','Task 2');
INSERT INTO tEvent(Id, ParentId, Name) VALUES ('6','1','Task 3');

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_NAME] = 'tBooking')
BEGIN
    DROP TABLE [tBooking]
END

CREATE TABLE [dbo].[tBooking] 
(
    [Id] NVARCHAR (50) NOT NULL, 
    [EventId] NVARCHAR (50) NOT NULL,
    [Value] [FLOAT]
);

INSERT INTO tBooking(Id, EventId, Value) VALUES ('1','1','4');
INSERT INTO tBooking(Id, EventId, Value) VALUES ('2','2','5');
INSERT INTO tBooking(Id, EventId, Value) VALUES ('3','2','10');
INSERT INTO tBooking(Id, EventId, Value) VALUES ('4','3','8');
INSERT INTO tBooking(Id, EventId, Value) VALUES ('5','4','5');
INSERT INTO tBooking(Id, EventId, Value) VALUES ('6','4','15');
INSERT INTO tBooking(Id, EventId, Value) VALUES ('7','5','18');
INSERT INTO tBooking(Id, EventId, Value) VALUES ('8','6','40');
INSERT INTO tBooking(Id, EventId, Value) VALUES ('9','6','12');
INSERT INTO tBooking(Id, EventId, Value) VALUES ('10','6','9');

My query:

;WITH CTE AS 
(
    SELECT 
        A.Id,
        SUM(B.Value) Value,
        A.Id Root
    FROM tEvent A
        LEFT JOIN tBooking B
            ON A.Id = B.EventId
    GROUP BY A.Id

    UNION ALL

    SELECT 
        A.Id,
        B.Value,
        CTE.Id
    FROM tEvent A
        INNER JOIN CTE
            ON A.ParentId = CTE.Id
        INNER JOIN tBooking B
            ON A.Id =  B.EventId
)
SELECT * FROM CTE;

Here is the sqlfiddle.

Any help is very much appreciated.

DhruvJoshi DhruvJoshi · Accepted Answer · 2020-09-10T07:33:09

You can sum up like below. So in your Fiddle I see that possibly name and parentid columns have been reversed. Below query is based on your fiddle. here's link to your modified fiddle as well

; with bookingtotal as 
(
select 
       Eventid,
       value = sum(value) 
from tBooking 
group by EventId
), 
recursiontree as 
(
select  
    ParentId=id,
    eventid=id 
from  tEvent  
  union all
select  
    id=case when T.ParentId<>'' then T.ParentId else t.id end, 
    eventId=e.eventid 
from recursiontree e 
    join tEvent t on e.ParentId=t.Id
    where T.ParentId<>''
)
, eventTotal as 
(select ParentId, sum(value) as value
 from recursiontree e
  join bookingtotal b on e.eventId=b.Eventid
  group by ParentId
 )

select e.*,value from eventTotal t join tEvent e
on e.id=t.ParentId

Tree structure recursive sum of aggregated values from another table

1 Answers