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

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

Answer 2

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]

Answer 3

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

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]

Answer 4

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 SummedInv subquery is grouped on project, it's safe to group on SummedInv.SUM_INV in the outer query as well.

Answer 5

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]

Answer 6

If you have N rows in AP with a given project ID, and M rows in INV with that ID, then the join between the two tables on the project ID will have a total of N*M rows 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).

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.