来自多个表的SQL求和数据 [英] sql sum data from multiple tables

问题描述

我有2个表AP和INV，它们都具有[PROJECT]和[Value]列.

我希望查询返回类似这样的内容:

项目| SUM_AP | SUM_INV

我想出了下面的代码，但是返回的结果是错误的(总和是错误的).

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]

解决方案

查询的结果是错误的，因为您要汇总的值被分组，这导致重复的值包含在SUM中. /p>

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

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]

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

I want a query to return something like this :

PROJECT | SUM_AP | SUM_INV

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

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]

解决方案

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.

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]

这篇关于来自多个表的SQL求和数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！