How to sum two query column without double counting

Question

I have table 1 include, projectID, PO#, POcost

table2 include, project ID, invoice#, invoice cost. PO# 

in table2, the PO# could happen multiple times, coz we can pay multiple times to pay the full amount of the original PO(in table1).

Now I want to get a form list

project ID, sumofPOcost(from table 1), sumof invoicecost (from table2), variance between POcost and invoice cost. I can get table 1's PO#key words. 

My question is for the sumofPOcost I can use group by, so it won't duplicate based on how many matching invoice record there. But how I can get the variance based on each project without counting too many times' POcost?

Thank you very much!

Answer 1

You'll need to join table 1 to a subquery which returns the total invoice cost per project, e.g.

SELECT [table 1].[projectID], SUM([POcost]) AS TotalPOCost,
[TotalInvoiceCost], SUM([POcost]) - [TotalInvoiceCost] AS Variance
FROM [table 1] INNER JOIN
    (SELECT [project ID], SUM([invoice cost]) AS [TotalInvoiceCost]
     FROM [table 2]
     GROUP BY [project ID]) AS [TotalInvoiceCosts]
ON [table 1].[projectID] = [TotalInvoiceCosts].[project ID]
GROUP BY [table 1].[projectID];

Note that I've reproduced the inconsistency in column names between the two tables.  Whether these are actual inconsistencies or merely typos in your post I cannot be sure.

---

Ken Sheridan, Stafford, England

Answer 2

Hi flowerkacici,

Is your issue solved?

I find that you did not follow up this thread after posting the issue.

If your issue is solved then I suggest you to post your solution and mark it as an answer.

If your issue is still exist then try to refer the solution given by the community members.

If then also you have any further questions then let us know about it.

We will try to provide further suggestions to solve the issue.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].