CODEWITHSUNDEEP
sqlmysql
D4M4H4
D4M4H4

Reputation: 429

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

Upvotes: 5

Views: 25425

Answers (3)

Santhosh
Santhosh

Reputation: 18

You can perform a simple join like below,

SELECT
    o.date AS order_date,
    o.status AS order_status,
    SUM(od.quanityordered * od.price) AS total_price
FROM orderdetails od
INNER JOIN order o
ON o.ordernumber = od.ordernumber
GROUP BY order_date, order_status

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Your approach is reasonable. But it should look like this:

SELECT o.orderDate, o.status,
       (SELECT SUM(od.quantityOrdered * od.priceEach)
        FROM orderdetails ol
        WHERE od.orderNumber = o.orderNumber
       ) as Prices
FROM orders o;

This is called a correlated subquery. Note the WHERE clause instead of the GROUP BY.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

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

Upvotes: 7

Related Questions