Sql error ERROR 1241 (21000): Operand should contain 1 column(s)

Question

Find the top 10 accounts in terms of highest total_amt_usd:

SELECT 
    AVG(total_amt_usd)
FROM
    orders
WHERE 
    total_amt_usd = (SELECT A.id, A.name, SUM(O.total_amt_usd) tot_spent
                     FROM orders O
                     JOIN accounts A ON A.id = O.account_id
                     GROUP BY A.id, A.name
                     ORDER BY 3 DESC
                     LIMIT 10)

You are trying to compare "otal_amt_usd" with "A.id, A.name, SUM(O.total_amt_usd)"... — jarlh, Mar 11 '21 at 21:47
It is duplicate. Same type of problem. [MySQL - Operand should contain 1 column(s)](https://stackoverflow.com/questions/14046838/mysql-operand-should-contain-1-columns) — Musselman, Mar 11 '21 at 21:49

score 0 · Answer 1 · answered Mar 11 '21 at 21:53

0

I can't check it now, but I guess the reason that in subquery you're selecting 3 fields. You can't assign to "total_amt_usd" result of 3 fields, you need only one value for "total_amt_usd".

answered Mar 11 '21 at 21:53

Oleg Skvortsov

1
2

score 0 · Accepted Answer · answered Mar 12 '21 at 05:49

You can not compare total_amt_usd with three values A.id, A.name, SUM(O.total_amt_usd)

like total_amt_usd =A.id, A.name, SUM(O.total_amt_usd)

To get sum of top 10 order with highest total_amt_usd you can try below code:

select sum(total_amt_usd) from (
SELECT A.id, A.name, O.total_amt_usd 
                     FROM orders O
                     JOIN accounts A ON A.id = O.account_id
                     order by total_amt_usd desc
                     LIMIT 10) t

Sql error ERROR 1241 (21000): Operand should contain 1 column(s)

2 Answers2