Sum values from child tables and ignore equal rows

Question

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?

Answer 1

Like @ypercube suggest, aggregate both sides separately, then join them.

In the following code, I've intentionally used LEFT JOIN to cater for the possibility that a project may not have a benefit or cost.

WITH ben AS (
    SELECT pbc.project_id, SUM(b.project_benefit) AS TOTAL_BENEFIT
    FROM Benefit AS b
    INNER JOIN ProjectBenefitCost AS pbc ON b.benefitcost_id=pbc.id
    GROUP BY pbc.project_id),

     cost AS (
    SELECT pbc.project_id, SUM(c.project_cost) AS TOTAL_COST
    FROM Cost AS c
    INNER JOIN ProjectBenefitCost AS pbc ON c.benefitcost_id=pbc.id
    GROUP BY pbc.project_id)

SELECT P.id, P.name, cost.TOTAL_COST, ben.TOTAL_BENEFIT
FROM Project AS P
LEFT JOIN ben ON P.id=ben.project_id
LEFT JOIN cost ON P.id=cost.project_id;

Answer 2

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.

Answer 3

SELECT P.id, P.name 
     , isnull(c.sum, 0) AS TOTAL_COST
     , isnull(b.sum, 0) AS TOTAL_BENEFIT 
     , isnull(b.sum, 0) - isnull(c.sum, 0) AS delta 
  FROM Project AS P 
  LEFT JOIN ( select BC.project_id, sum(Cost.project_benefit) as sum  
                from ProjectBenefitCost AS BC 
                JOIN Cost 
                      ON BC.id = Cost.benefitcost_id 
               GROUP BY BC.project_id
       ) as c 
         ON c.project_id = P.id
  LEFT JOIN ( select BC.project_id, sum(Benefit.project_benefit) as sum  
                from ProjectBenefitCost AS BC 
                JOIN Benefit 
                      ON BC.id = Benefit.benefitcost_id 
               GROUP BY BC.project_id
       ) as b 
         ON b.project_id = P.id;

I think this is cleaner as one less join

SELECT P.id, P.name 
     , isnull(c.sum, 0) AS TOTAL_COST
     , isnull(b.sum, 0) AS TOTAL_BENEFIT 
     , isnull(b.sum, 0) - isnull(c.sum, 0) AS delta 
  FROM Project AS P 
  JOIN ProjectBenefitCost AS BC 
        ON P.id = BC.project_id
  LEFT JOIN ( select benefitcost_id, sum(project_benefit) as sum  
                from Cost 
               GROUP BY benefitcost_id
       ) as c 
         ON c.benefitcost_id = BC.id
  LEFT JOIN ( select benefitcost_id, sum(project_benefit) as sum  
                FROM Benefit 
               GROUP BY benefitcost_id
       ) as b 
         ON b.benefitcost_id = BC.id;