[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Returning Total Inventory Quantity from the AdventureWorks Database

Posted on 2006-07-06
2
Medium Priority
?
279 Views
Last Modified: 2008-03-10
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!

0
Comment
Question by:BoggyBayouBoy
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 17052435
SELECT s.ProductID, s.UnitPrice, s.unitPriceDiscount, sum(pri.Quantity) as 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
group by s.ProductID, s.UnitPrice, s.unitPriceDiscount, p.Name
0
 
LVL 1

Author Comment

by:BoggyBayouBoy
ID: 17052692
Sweet!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question