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 Cost AS C ON
      BC.id = C.benefitcost_id
    LEFT JOIN Benefit AS B ON
      BC.id = B.benefitcost_id
GROUP BY P.id, P.name


It will return 100000.00 as the TOTAL_BENEFIT for the Project B, because this Project has two related rows in the Cost table.

This is just the first step that needs to be solved before I can subtract TOTAL_COST from TOTAL_BENEFIT.

How can I fix this SQL statement?
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.