Suppose I had two tables, orderdetails and orders.
I would like to print orderDate, the status of the order & the total price.
In the orderdetails table, there is orderNumber, quantityOrdered, priceEach & productCode.
In the orders, there is status & orderNumber.
I have tried this code but it will not work.
SELECT orderDate, status, (SELECT SUM(quantityOrdered * priceEach) AS prices FROM orderdetails GROUP BY orderNumber)
FROM orders o, orderdetails od
WHERE od.orderNumber = o.orderNumber
Use a join:
SELECT t1.orderDate, t1.status, t2.prices FROM orders t1 INNER JOIN ( SELECT orderNumber, SUM(quantityOrdered * priceEach) AS prices FROM orderdetails GROUP BY orderNumber ) t2 ON t1.orderNumber = t2.orderNumber