join - sql sum data from multiple tables

Question

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]

Answer 1

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]