Sum values from child tables and ignore equal rows

I have two child tables with a value that must be summed separately and at the end the totals must be subtracted. The problem is that Child Table A and Child Table B can have 0 or more rows associated to the same Parent Table row, then some rows are calculated twice.

Here is the SQL to create the sample DB:

CREATE TABLE Project (
  id int,
  name varchar(50),
  CONSTRAINT PK_Project PRIMARY KEY (id)
  );

  CREATE TABLE ProjectBenefitCost (
    id int,
    project_id int,
    CONSTRAINT PK_ProjectBenefitCost PRIMARY KEY(id),
    CONSTRAINT FK_BenefitCost_Project FOREIGN KEY(project_id) REFERENCES Project(id)
    );

  CREATE TABLE Cost (
    id int identity(1,1),
    project_cost numeric(9,2),
    benefitcost_id int,
    CONSTRAINT PK_Cost PRIMARY KEY(id),
    CONSTRAINT FK_Cost_BenefitCost FOREIGN KEY(benefitcost_id) REFERENCES ProjectBenefitCost(id)
    );

    CREATE TABLE Benefit (
    id int identity(1,1),
    project_benefit numeric(9,2),
    benefitcost_id int,
    CONSTRAINT PK_Benefit PRIMARY KEY(id),
    CONSTRAINT FK_Benefit_BenefitCost FOREIGN KEY(benefitcost_id) REFERENCES ProjectBenefitCost(id)
    );

    INSERT INTO Project (id, name) values (1, 'Project A');
    INSERT INTO Project (id, name) values (2, 'Project B');

    INSERT INTO ProjectBenefitCost (id, project_id) VALUES (1,1);
    INSERT INTO ProjectBenefitCost (id, project_id) VALUES (2,2);

    INSERT INTO Cost (project_cost, benefitcost_id) VALUES (5006.20, 1);
    INSERT INTO Cost (project_cost, benefitcost_id) VALUES (10000.10, 2);
    INSERT INTO Cost (project_cost, benefitcost_id) VALUES (2000.10, 2);

    INSERT INTO Benefit (project_benefit, benefitcost_id) VALUES (3000.00, 1);
    INSERT INTO Benefit (project_benefit, benefitcost_id) VALUES (50000.00, 2);

If I use this SQL:

```
SELECT P.id, P.name, SUM(C.project_cost) AS TOTAL_COST, SUM(B.project_benefit) AS TOTAL_BENEFIT
FROM
Project AS P INNER JOIN ProjectBenefitCost AS BC ON
P.id = BC.project_id
LEFT JOIN Co

Use:

SELECT *
 FROM Project AS P 
OUTER APPLY
(SELECT SUM(C.project_cost) AS TOTAL_COST
    FROM Cost AS C
    JOIN ProjectBenefitCost AS BC 
    ON P.id = BC.project_id
    AND BC.id = C.benefitcost_id) C
 OUTER APPLY
 (SELECT SUM(B.project_benefit) AS TOTAL_BENEFIT
    FROM Benefit AS B
    JOIN ProjectBenefitCost AS BC 
    ON P.id = BC.project_id
    AND BC.id = B.benefitcost_id) B
;

You can't have your join make multiple copies of your project row without also making multiple copies of your benefit row. So when you join through to cost and benefit, you need to aggregate the rows there down to a single row per project before the join kicks in.

StackExchange Database Administrators Q#126775, answer score: 3