Subquery returns more than 1 row in select statement

database, mysql, sql

Solution

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

Problem

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 ```

Original source