SQL Server Select sum of item from 2 tables

Question

I have 2 tables; Inbound & Outbound. Both of them have the same structure.

TransactionId (int),
Date (datetime),
StoreItemId (int) [FK],
Quantity (real)

I tried this select statement and the result as in the image below:

SELECT [StoreItemId], SUM([Quantity]) AS 'inbound' FROM [Inbound] GROUP BY [StoreItemId];
SELECT [StoreItemId], SUM([Quantity]) AS 'outbound' FROM [Outbound] GROUP BY [StoreItemId];

I tried a query:

SELECT
    (SELECT SUM([Quantity]) AS 'inbound' FROM [Inbound] WHERE [Inbound].[StoreItemId] = 1) - 
    (SELECT SUM([Quantity]) AS 'Outbound' FROM [Outbound] WHERE [Outbound].[StoreItemId] = 1)

and the result was 105 (115-10 ; Inbound quantity for StoreItemId = 1 - Outbound quantity for StoreItemId = 1)

I tried combining both queries above to a query like this:

SELECT [StoreItemId] AS 'xyz'
       (SELECT SUM([Quantity]) FROM [Inbound] WHERE [Inbound].[StoreItemId] = [xyz]) - 
       (SELECT SUM([Quantity]) FROM [Outbound] WHERE [Outbound].[StoreItemId] = [xyz])
FROM [StoreItem]

But it's just wrong

The result should be:

StoreItemId | Balance
----------------------
1           |   105
2           |   126
3           |   78
4           |   144
5           |   100
6           |   179

Possible duplicate of formula for computed column based on different table's column — Tanner
– Tanner, Commented Sep 11, 2018 at 12:38
I made this statement so far SELECT [StoreItemId], SUM([Quantity]) AS 'inbound' FROM [Inbound] GROUP BY [StoreItemId]; the same for outbound but I don't know how to subtract the 2 quantities from each other — Taher
– Taher, Commented Sep 11, 2018 at 12:42
you might want to re-consider this. When the number of transaction growth, there might be performance issue — Squirrel
– Squirrel, Commented Sep 11, 2018 at 13:07
@Squirrel well that changes things, I think I will have to remove the balance table and calculate the balance at the client side inside the Application codes — Taher
– Taher, Commented Sep 11, 2018 at 13:14

Bhargav J Patel · Accepted Answer · 2018-09-12 05:25:49Z

0

You can use LEFT JOIN With GROUP BY AND SUM of QUANTITY. You can Refer below Query For same.

select StoreItemId  , (sum(ISNULL(inb.Quantity,0)) - sum(ISNULL(ob.Qunatity,0))) as Balance
from Inbound inb 
LEFT JOIN Outbound ob on ob.StoreItemId = si.StoreItemId
GROUP BY StoreItemId

answered Sep 12, 2018 at 5:25

Bhargav J Patel

1661 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taher Over a year ago

still returning wrong quantities, it is returning: (1: 305) / (2: 504) / (3: 78) / (4: 138) / (5: 100) / (6: 178)

Collectives™ on Stack Overflow

SQL Server Select sum of item from 2 tables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related