SELECT + SUM + LEFT JOIN

Question

I was hoping to receive some advice. I need to work out how many projects do not have partner allocations of 100%.

One project can have many partners.

The project and partner tables are connected by the ProjectID.

I need to select project id, project name FROM project where partner_% != 100%.

So I was thinking along the lines of

SELECT project id, project name, SUM (partner_%) as [Project Partner Total]
FROM Project Table
  LEFT JOIN [partner table]
  ON project.projectID = partner.projectID

Would my the above work okay for this? (It is SQL Server 2014).

Answer 1

If I understand correctly, you need a group by and a having clause:

SELECT project id, project name, SUM(partner_%) as [Project Partner Total]
FROM Project Table LEFT JOIN
     [partner table]
     ON project.projectID = partner.projectID
GROUP BY project id, project name
HAVING SUM(partner_%) <> 100;