CODEWITHSUNDEEP
sqlms-access
Doug Coats
Doug Coats

Reputation: 7107

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"

Upvotes: 1

Views: 540

Answers (4)

dbmitch
dbmitch

Reputation: 5386

I can only assume what your tables look like since you haven't included those relationships or that information, but from my experience there is probably only one Inventory record for items, and multiple records in Invoices.

So with that assumption in mind, you'd want to subtract all sum of all the Invoice entries for one item from the one item record matching it in Inventory.

And finally - TotalNeeded - doesn't make sense to me for field name - it really reflects "TotalRemaining"

And finally finally... I assume you want ALL inventory - not just invoiced

With ALL that in mind - this might work

SELECT Inventory.NameOfItem, (Inventory.Volume - nz(t2.TotalSold,0)) As TotalRemaining
FROM Inventory
LEFT JOIN
(SELECT Invoice.NameOfItem, Sum(Invoice.Quantity) AS TotalSold
FROM Invoice
GROUP BY Invoice.NameOfItem) t2
ON Inventory.NameOfItem = t2.NameOfItem

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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
     ) as invo INNER JOIN
     (SELECT NameOfItem, SUM(Quantity) as inventory
      FROM Inventory
      GROUP BY NameOfItem
     ) as inve
     ON Invo.NameOfItem = Inve.NameOfItem;

Upvotes: 1

Dmitry Ziyatdinov
Dmitry Ziyatdinov

Reputation: 95

When you're joining Inventory to the Invoice, for every quantity with the same NameOfItem you get it's Volume from the Inventory.

So, say, you have following data [{NameOfItem: 'A', Quantity: 1}, {NameOfItem: 'A', Quantity: 2}, {NameOfItem: 'B', Quantity: 1}] in the Invoice table, and [{NameOfItem: 'A', Volume: 5}, {NameOfItem: 'B', Volume: 6}] in the Inventory table.

After the join you'll have [{NameOfItem: 'A', Quantity: 1, Volume: 5}, {NameOfItem: 'A', Quantity: 2, Volume: 5}, {NameOfItem: 'B', Quantity: 1, Volume: 6}]

Hope, it's obvious now, that you need to group data in the Invoice table beforehand.

Upvotes: 1

Kyle
Kyle

Reputation: 115

I think you need to give the tables an alias

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

Upvotes: 1

Related Questions