15,314,282 members
See more:
```Dear All,

We have 2 tables where the total items details store in Table1.In Table 2 the received items details storing.

Eg:-

Table 1

ID	ITEM	QTY
1	I1	2
2	I1	2
3	I2	10

Table2

ID   ITEM	QTY
1	I1	10
2	I2	10

I need the output like below

ITEM	Qty	BALANCE
I1	4	6
I2	0	10```

What I have tried:

I write to join the 2 tables if I1 exists in Table1 then the balance will fetch as perfect.

Item which not exists in table1 is not displaying we need the item balance for the Item2 too in the output.

Kindly help
Posted
Updated 25-Jun-21 19:10pm

## Solution 1

Use GROUP BY to combine the items in Table1, and then JOIN them to Table2.
The GROUP BY can use aggregate functions - including SUM - to get the quantity per item.

SQL
```SELECT a.Item, a.QTY, b.QTY - a.QTY AS Balance
FROM (SELECT Item, SUM(QTY) AS QTY FROM Table1
GROUP BY Item) a
JOIN Table2 b ON a.Item = b.Item```
Member 11658469 26-Jun-21 1:34am

This will fetch the balance where both tables contains.I need to get the item count from table2 which not exists in table 1
OriginalGriff 26-Jun-21 1:40am

I tested it with the input you provided, and compared it to the output you requested: it matches.

You need to explain yourself better, or provide sample data that actually shows what you want!

Member 11658469 26-Jun-21 1:49am

Thanks for the response.Sorry the exact example below

Table 1

ID ITEM QTY
1 I1 2
2 I1 2
3 I2 10

Table2

ID ITEM QTY
1 I1 10
2 I2 10
3 I3 10
I need the output like below

ITEM Qty BALANCE
I1 4 6
I2 10 0
I3 0 10

I3 exists in Table2 which not exists in Table1

The output should be mentioed above
OriginalGriff 26-Jun-21 2:12am

That's pretty simple - what did you try?
Sorry, I'd give you the solution - it only took a couple of seconds to do - but your attitude offends, so I want to be sure you aren't a lazy student trying to get me to do his homework ... :laugh:
Member 11658469 26-Jun-21 2:26am

Kindly help plz
OriginalGriff 26-Jun-21 3:12am

I will - but that doesn't mean we are here to do your homework for yoU!
So ... what have *you* tried?