Doug Coats Doug Coats - 1 year ago 53
SQL Question

SUM two different tables columns in SQL ACCESS

Trying to figure out how to get this JOIN to work properly. Been sitting here for about 30 minutes. Can someone help me out? I am trying to subtract one form the other, to see the difference between invoice quantity and inventory volume.

SELECT Invoice.NameOfItem, SUM(Inventory.Volume - Invoice.Quantity) As TotalNeeded
FROM Invoice
INNER JOIN Inventory
ON Invoice.NameOfItem=Inventory.NameOfItem
GROUP BY Invoice.NameOfItem;

The issue is the output is incorrect.

SELECT NameOfItem, SUM(Quantity) AS TotalNumberNeeded From Invoice GROUP BY NameOfItem

subtracted from

SELECT NameOfItem, SUM(Volume) AS TotalNumberNeeded From Inventory GROUP BY NameOfItem

Is = -112. The output is currently "992"

Answer Source

You need to aggregate the data before doing the join. In MS Access, you can do:

SELECT Invo.NameOfItem, (inventory - invoice) As TotalNeeded
FROM (SELECT NameOfItem, SUM(Volume) as invoice
      FROM Invoice
      GROUP BY NameOfItem
     ) invo INNER JOIN
     (SELECT NameOfItem, SUM(Quantity) as inventory
      FROM Inventory
      GROUP BY NameOfItem
     ) inve
     ON Invo.NameOfItem = Inve.NameOfItem;