CODEWITHSUNDEEP
sql-server
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

How to get Product wise stock using sql server query

I am working on an sql query where i have fllowing tables

tblProducts (ProductID,Name,Code,Features)
tblStock    (StockID,ProductID,Stock,Date)
tblOrderDetails(DetailID,ProductID,ProductQuantity,Date)

User enters the Stock of any Product on arrival of Its stock. When user buys any product in any quantity then its details stored in tblOrderDetails table. I want the total stock Products Group by ProductID and also the Order of Each Product group wise Product wise. For this i have used following query;- 

SELECT
    tblStock.ProductID,
    ISNULL(SUM(tblOrderDetails.Qty), 0) AS used,
    SUM(tblStock.Stock) AS st
FROM tblStock
INNER JOIN tblProducts
    ON tblProducts.ProductID = tblStock.ProductId
LEFT OUTER JOIN tblOrderDetails
    ON tblOrderDetails.ProductID = tblStock.ProductId
GROUP BY tblStock.ProductID

in the Order detail table i have two order of two Product id. the above query is showing the sum of ordered Product of only one Product not the second one. How to get the approproate ordered Product ProductID wise and total stock also ProductID wise.. How i can achieve this?

Upvotes: 0

Views: 1719

Answers (1)

James Z
James Z

Reputation: 12318

You can't join tables just with product id because having several orders and stock for same product will multiply your results, so you'll have to do for example something like this:

; with 
O as (select ProductId, SUM(Qty) AS used from tblOrderDetails group by ProductId),
S as (select ProductId, SUM(Stock) as st from tblStock group by ProductId)

SELECT
    P.ProductID,
    ISNULL(O.used), 0) AS used,
    S.st
FROM tblProducts P
LEFT OUTER JOIN O on O.ProductId = P.ProductId
LEFT OUTER JOIN S on S.ProductID = P.ProductId

Upvotes: 1

Related Questions