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

mysql subquery operand mysql-error-1241
Marco picture Marco · Nov 8, 2013 · Viewed 31.9k times · Source

I have Customer Groups with Number-Ranges (from Customernumber, to Customernumber).

select g.id,
(select count(*), sum(sales)
FROM transactions t1 
where t1.customernumber between g.from_customernumber and g.to_customernumber)
from customer_groups g

When selecting this i get this error

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

What can i do to fix this? I've read some threads about this but I didn't find a solution for this.

Best regards!

Answer

fthiella picture fthiella · Nov 8, 2013

MySQL is expecting a single column from your subquery, i.e. the SELECT in the brackets can only SELECT for a single column.

In your example, you could use two subqueries, one that returns the count and one other that returns the sum, but you could also rewrite your query as this:

SELECT g.id, COUNT(t1.customernumber), SUM(sales)
FROM
  customer_groups g LEFT JOIN transactions t1
  ON t1.customernumber between g.from_customernumber and g.to_customernumber

Related questions

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More
Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
What is the error "Every derived table must have its own alias" in MySQL?

I am running this query on MySQL SELECT ID FROM ( SELECT ID, msisdn FROM ( SELECT * FROM TT2 ) ); and it is giving this error: Every derived table must have its own alias. What's causing this error?

Read More