Left Join and Sum Each Individual Row

Question

I have a table named orders (orderID, customerID, purchaseDate, and paymentType) and orderLine (orderID, upc, productName, quantity, price). I'm trying to join these two tables so that it primarily shows the orders table but then has a total price column for each order on the far right. This is what I have so far but it's adding the total for every single row into one. I want each individual row to have their own sum by multiplying quantity*price based on the orderID.

SELECT orders.orderID, SUM(orderLine.price * orderLine.quantity) 
FROM orderLine 
LEFT JOIN orders 
ON orders.orderID = orderLine.orderID

Answer 1

You need to add group by clause

SELECT orders.orderID, SUM(orderLine.price * orderLine.quantity) 
FROM orderLine 
LEFT JOIN orders 
ON orders.orderID = orderLine.orderID
grou by orders.orderID

Answer 2

SELECT orders.orderID, (SUM(orderLine.price * orderLine.quantity) Over (Order 
By orders.orderID))
FROM orderLine 
LEFT JOIN orders 
ON orders.orderID = orderLine.orderID
Group By orders.orderID

Answer 3

You want all orders, so start with orders. Table aliases also make the query easier to write and to read:

SELECT o.orderID, SUM(ol.price * ol.quantity) 
FROM orders o LEFT JOIN
     orderLine ol
     ON o.orderID = ol.orderID
GROUP BY o.orderID;

Then the answer to your question is GROUP BY.

You would have a very poor data model if you have orderlines that don't have a corresponding order, which is what your version of the query suggests.