CODEWITHSUNDEEP
sql
Keval savani
Keval savani

Reputation: 55

I need product Stock quantity from two tables

I have two tables:

  1. product_in (store all product QTY)

    Product Code(PK)     Description                QTY
RS121102             SUITS                      100
RS129985             SUITS                      100
DF-C09               SHIRTS                     50
AE-H05               SHIRTS                     50

  2. product_out (store all products sold QTY)

    Product Code         Description                QTY
RS121102             SUITS                      50
AE-H05               SHIRTS                     10

I want result like below

Product Code         Description        Total Qty        Sold QTY
RS121102             SUITS                  100            50
RS129985             SUITS                  100            0
DF-C09               SHIRTS                 50             0
AE-H05               SHIRTS                 50             10

How can I do this?

Upvotes: 0

Views: 335

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

Try this: 

SELECT ProductCode, Description, SUM(Total_QTY) AS Total_Qty, SUM(Sold_Qty) AS Sold_Qty
FROM 
(
     SELECT ProductCode, Description, QTY As Total_Qty, 0 As Sold_Qty
     from product_in
)
UNION ALL
(
     SELECT ProductCode, Description,  -QTY As Total_Qty, QTY As Sold_Qty
     from product_out
)
GROUP BY ProductCode, Description

Upvotes: 0

GJKH
GJKH

Reputation: 1725

SELECT pi.ProductCode, pi.Description, pi.QTY AS TotalQty, 
ISNULL(po.QTY, 0) AS SoldQty
FROM product_in as pi
LEFT JOIN product_out as po
ON po.ProductCode = pi.ProductCode

That's asuming there aren't multiple records for each product in product_out.

Upvotes: 2

Related Questions