SQL sql汇总多个表中的数据

sql sum data from multiple tables

提问by Iulian

I have 2 tables AP and INV where both have the columns [PROJECT] and [Value].

我有 2 个表 AP 和 INV，其中都有 [PROJECT] 和 [Value] 列。

I want a query to return something like this :

我想要一个查询返回这样的东西：

PROJECT | SUM_AP | SUM_INV

项目 | SUM_AP | SUM_INV

I came up with the code below but it's returning the wrong results ( sum is wrong ).

我想出了下面的代码，但它返回了错误的结果（ sum 是错误的）。

SELECT AP.[PROJECT],
SUM(AP.Value) AS SUM_AP, 
SUM(INV.Value) AS SUM_INV
FROM AP INNER JOIN INV ON (AP.[PROJECT] =INV.[PROJECT])
WHERE AP.[PROJECT] = 'XXXXX'
GROUP BY AP.[PROJECT]

回答by Prutswonder

The results from your query are wrong because the values you are trying to summarize are being grouped, which causes duplicate values to be included in the SUM.

您的查询结果是错误的，因为您尝试汇总的值被分组，这会导致重复的值包含在SUM.

You could solve it with a couple of sub-selects:

您可以通过几个子选择来解决它：

SELECT 
    AP1.[PROJECT],
    (SELECT SUM(AP2.Value) FROM AP AS AP2 WHERE AP2.PROJECT = AP1.PROJECT) AS SUM_AP,
    (SELECT SUM(INV2.Value) FROM INV AS INV2 WHERE INV2.PROJECT = AP1.PROJECT) AS SUM_INV
FROM AP AS AP1 
    INNER JOIN INV AS INV1 
        ON (AP1.[PROJECT] =INV1.[PROJECT])
WHERE AP1.[PROJECT] = 'XXXXX'
GROUP BY AP1.[PROJECT]

回答by Amber

If you have Nrows in AP with a given project ID, and Mrows in INV with that ID, then the join between the two tables on the project ID will have a total of N*Mrows for that project, because the same row in AP will be repeated for every row in INV that has that project ID, and vice versa. Hence why your counts are most likely off (because it's counting the same row in a given table multiple times due to repetition from the join).

如果NAP 中的行具有给定的项目 ID，而MINV 中的行具有该 ID，则项目 ID 上的两个表之间的连接将具有该项目的N*M总行数，因为 AP 中的同一行将重复对于 INV 中具有该项目 ID 的每一行，反之亦然。因此，为什么您的计数最有可能关闭（因为由于连接的重复，它多次计算给定表中的同一行）。

Instead, you might want to try doing a join between the results of two subqueries, one which groups the first table by project ID and does that its sum, and the second which groups the other table by project ID and does that sum - then joining once you only have 1 row with sum for each project ID.

相反，您可能想要尝试在两个子查询的结果之间进行连接，一个按项目 ID 对第一个表进行分组并计算总和，第二个按项目 ID 对另一个表进行分组并计算总和 - 然后加入一旦您只有 1 行，每个项目 ID 的总和。

回答by Marcus Adams

If PROJECT is the parent table, you should select FROM the project table, and do a left outer join on the two child tables:

如果 PROJECT 是父表，则应从项目表中选择，并对两个子表执行左外连接：

SELECT PROJECT.PROJECT_ID, SUM(AP.Value) AS SUM_AP, SUM(INV.Value) AS SUM_INV
FROM PROJECT
LEFT OUTER JOIN AP ON (AP.[PROJECT] = PROJECT.[PROJECT_ID])
LEFT OUTER JOIN INV ON (INV.[PROJECT] = PROJECT.[PROJECT_ID])
WHERE PROJECT.[PROJECT_ID] = 'XXXXX'
GROUP BY PROJECT.[PROJECT_ID]

回答by Andomar

You could separate the two sum calculations. One way I can think of is to move the inventory calculation to a subquery, like:

您可以将两个总和计算分开。我能想到的一种方法是将库存计算移动到子查询，例如：

SELECT 
    AP.[PROJECT]
,   SUM(AP.Value) AS SUM_AP
,   SummedInv as SUM_INV
FROM AP
LEFT JOIN (
    SELECT PROJECT, SUM(Value) AS SUM_INV
    FROM INV
    GROUP BY PROJECT
) SummedInv ON SummedInv.Project = AP.Project
GROUP BY AP.PROJECT, SummedInv.SUM_INV

Because the SummedInvsubquery is grouped on project, it's safe to group on SummedInv.SUM_INVin the outer query as well.

因为SummedInv子查询分组在 上project，所以SummedInv.SUM_INV在外部查询中分组也是安全的。

回答by Mikko Fullero

how about this query :

这个查询怎么样：

select SUM(gpCutBody.actualQty) as cutQty   , SUM(gpSewBody.quantity) as sewQty

from jobOrder
inner join gpCutHead on gpCutHead.joNum = jobOrder.joNum
inner join gpSewHead on gpSewHead.joNum = jobOrder.joNum

inner join gpCutBody on gpCutBody.gpCutID = gpCutHead.gpCutID
inner join gpSewBody on gpSewBody.gpSewID = gpSewHead.gpSewID


where jobOrder.joNum = '36'

here is the link to the ERD: http://dl.dropbox.com/u/18794525/AUG%207%20DUMP%20STAN.png

这是 ERD 的链接：http: //dl.dropbox.com/u/18794525/AUG%207%20DUMP%20STAN.png

回答by lugte098

Try:

尝试：

SELECT AP.[PROJECT] AS PROJECT, SUM(AP.[Value]) AS SUM_AP, SUM(INV.[Value]) AS SUM_INV
FROM AP, INV
WHERE AP.[PROJECT] = INV.[PROJECT]
AND AP.[PROJECT] = 'XXXXX'
GROUP BY AP.[PROJECT]