如何在不重复计数的情况下对两个查询列求和 [英] How to sum two query column without double counting

问题描述

我有表1包含，projectID，PO＃，POcost

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

table2包含，项目ID，发票号，发票成本。表2中的PO＃ 

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

，PO＃可能会多次发生，因为我们可以多次支付原始采购订单的全部金额（在表1中）。

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

项目ID，sumofPOcost（来自表1），sumof invoicecost（来自表2），POcost和发票成本之间的差异。   我可以获得表1的PO＃关键字。 

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. 

我的问题是sumofPOcost我可以使用group by，所以它不会根据有多少匹配的发票记录重复。但是如何根据每个项目获得方差而不计算'POcost太多次？

非常感谢！

推荐答案

您需要将表1加入一个子查询，它返回每个项目的总发票成本，例如



SELECT [表1]。[projectID]，SUM（[POcost]）AS TotalPOCost，

[TotalInvoiceCost]，SUM（[POcost]） - [TotalInvoiceCost] AS Variance

FROM [table 1] INNER JOIN

    （选择[项目ID]，SUM（[发票成本]）AS [TotalInvoiceCost]

     FROM [表2]

   ;   GROUP BY [项目ID]）AS [TotalInvoiceCosts]

ON [表1]。[projectID] = [TotalInvoiceCosts]。[项目ID]

GROUP BY [表1]。[projectID];

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.

这篇关于如何在不重复计数的情况下对两个查询列求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！