Damhan Richardson Damhan Richardson - 1 month ago 6
SQL Question

Subquery returns more than 1 row in select statement

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

Answer

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
Comments