Sql to fetch the qty balance

Question

0.00/5 (No votes)

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 25-Jun-21 18:57pm

Member 11658469

Updated 25-Jun-21 19:10pm

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2021-06-25T19:10:00

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

Posted 25-Jun-21 19:10pm

OriginalGriff

Comments

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?

Sql to fetch the qty balance

1 solution

Solution 1

Add your solution here

Preview 0

Sql to fetch the qty balance

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us