SQL triple joins, or how to distinctly combine order details

[EHaft]

Hi there,

I'm having some trouble attempting to return the total price for each order placed.

I'm working with the following tables:

Orders (OrderID, OrderDate, Location)

Sales (OrderID, ProductSold, QuantitySold)

Products (ProductName, SellPrice)

I'd like to distinctly select all unique Orders.OrderID entries, and tabulate the sum of each product sold per order.

My problem is that all values in Orders.OrderID are distinct (e.g. 1-20), but for Sales.OrderID there are multiple values per ID (Sales contains the OrderID in question, what product was sold, and how many; thus there may be multiple products [sales] per one order [orders].

Is there a simple solution for this that I'm missing? 

What I've got so far:

SELECT DISTINCT
Orders.OrderID, Sales.ProductSold, Sales.QuantitySold*Products.SellPrice AS 'Total Price'
FROM Sales
LEFT JOIN Orders ON Orders.OrderID = Sales.OrderID
LEFT JOIN Products ON Products.ProductName = Sales.ProductSold;

This returns multiple 'Total Prices' per 'ProductSold" for each OrderID.

Would there be a method way to sum 'Total Price' for each unique OrderID rather than for each ProductSold?

Otherwise:

Is there a way to combine the ProductSold and QuantitySold for each order, while still maintaining references to their respective ProductName and SellPrice? Is there a way to combine the information specific to each unique ID number or a way to concatanate all that information into a new table using SQL alone? 

Simply put, I'd like this query to return 20 records describing complete orders rather than 60 records describing each piece of an order.

Thanks for your help with this, I appreciate you!

[Anonymous · Accepted solution]

Hi @EHaft ,

You may try:

SELECT 
Orders.OrderID, SUM(Sales.QuantitySold*Products.SellPrice) AS 'Total Price'
FROM Sales
LEFT JOIN Orders ON Orders.OrderID = Sales.OrderID
LEFT JOIN Products ON Products.ProductName = Sales.ProductSold
GROUP BY Orders.OrderID

If this statement doesn't work on your end, could you please provide us with the table creation statement and sample data, as well as your expect result?

Note:Please be careful not to include sensitive information)

Best regards.
Community Support Team_Caitlyn