CODEWITHSUNDEEP
sql-servert-sql
Mr.Noroozi
Mr.Noroozi

Reputation: 11

my query doesn't work as expected

SELECT Product.ProductName
      ,SUM(Purchase.Value)              AS TotalPurchase
      ,SUM(Sales.Value)                 AS TotalSales
      ,((TotalPurchase) - (TotalSales)) AS ProductAvailability
FROM Product 
INNER JOIN Purchase ON Product.ProductID = Purchase.ProductID 
INNER JOIN Sales    ON Product.ProductID = Sales.ProductID
GROUP BY Product.ProductName

I have 3 Table Product - Sales - Purchase and i want Show product name total sales each product ,total purchase of each product And how many of those product still unsold

Upvotes: 0

Views: 36

Answers (2)

Mr.Noroozi
Mr.Noroozi

Reputation: 11

After 5 Hours OF playing With Query I FOUND THE ANSWER

SELECT  PN.ProductName,TotalPurchase ,TotalSales,TotalPurchase-TotalSales AS  ProductAvailability
FROM
((SELECT SUM(Purchase.Value) AS TotalPurchase,Purchase.ProductID FROM Purchase GROUP BY Purchase.ProductID ) AS TP
INNER JOIN 
(SELECT SUM(Sales.Value) AS TotalSales,Sales.ProductID FROM Sales GROUP BY Sales.ProductID ) AS TS
 ON tp.ProductID=TS.ProductID
 INNER JOIN
(SELECT Product.ProductName,Product.ProductID FROM Product GROUP BY Product.ProductName,ProductID) AS PN ON  PN.ProductID=TS.ProductID
)

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

SELECT Product.ProductName
      ,SUM(Purchase.Value)                                 AS TotalPurchase
      ,ISNULL(SUM(Sales.Value),0)                          AS TotalSales
      ,(SUM(Purchase.Value)  - ISNULL(SUM(Sales.Value),0)) AS ProductAvailability
FROM Product 
LEFT JOIN Purchase ON Product.ProductID = Purchase.ProductID 
LEFT JOIN Sales    ON Product.ProductID = Sales.ProductID
GROUP BY Product.ProductName

Since you also want to show the unsold products , there will be no data for them products in the sales table, hence you need a LEFT JOIN here not an INNER JOIN.

Also the columns TotalPurchase and TotalSales are available to be called in the query they are being calculated , hence use a sub-query to manipulate these columns or use the expression itself.

Upvotes: 1

Related Questions