Returning Total Inventory Quantity from the AdventureWorks Database
Posted on 2006-07-06
I would like to return a single row which returns the total items in stock for a given productID.
Currently my query is as follows:
SELECT s.SalesOrderID,s.SalesOrderDetailID, s.OrderQty, s.ProductID, s.UnitPrice, s.unitPriceDiscount,
s.LineTotal, pri.Quantity, p.Name as Name
from Sales.SalesOrderDetail s
INNER JOIN production.product p
ON p.ProductID = s.ProductID
inner join production.productInventory pri on pri.ProductID = s.ProductID
where s.SalesOrderID =@SalesOrderID
Because a single productID can exist in multiple locations, this query can return multiple rows - which I don't want.
Experts, How can I SUM the pri.Quantity and return a single row?? Thanks in advance!