Sql-server – Sum values from child tables and ignore equal rows

sql servert-sql

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?

Best Answer

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;
Related Question