Mysql ERROR 1241 (21000): The operand must contain 1 column (s)

Question

I have customer groups with numbers (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 choosing this, I get this error

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

What can I do to fix this? I read some topics about this, but I did not find a solution for this.

Yours faithfully!

Answer 1

MySQL expects one column from your subquery, i.e. SELECT in parentheses can only SELECT for one column.

In your example, you could use two subqueries, one that returns a counter, and another that returns a sum, but you can also rewrite your query as follows:

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