Getting error "Operand should contain 1 column(s)" on this query

Question

So I have the code below:

SELECT student_name FROM student S WHERE S.student_id IN
(SELECT t1.student_id, SUM(IF(t2.test_date IS NULL, 0, 1)) AS increase_score
FROM
test t1
LEFT JOIN test t2 
     ON t1.student_id = t2.student_id
     AND t1.test_date < t2.test_date
     AND t1.Score <= t2.Score
GROUP BY t1.student_id
HAVING 
     increase_score = 0
AND count(*) > 1)

I am getting an error "Operand should contain 1 column(s)". This only arises after adding the outer SELECT statement. I have confirmed the inner query is working as intended otherwise. I've looked at some other examples involving the same error, but I have not been able to determine what to do to fix it in this case.

Answer 1

The error is quite clear : you can't have more than one field in your sub query, you're macthing a field against a single column, obviously. So just move the SUM in the HAVING clause :

SELECT student_name
FROM student S
WHERE S.student_id IN
(SELECT t1.student_id
FROM test t1
LEFT JOIN test t2 
     ON t1.Name = t2.Name
     AND t1.Date < t2.Date
     AND t1.Score <= t2.Score
GROUP BY t1.Name
HAVING 
     SUM(IF(t2.test_date IS NULL, 0, 1)) = 0
AND count(*) > 1)

http://dev.mysql.com/doc/refman/5.7/en/any-in-some-subqueries.html

Answer 2

A query used as an argument to the in operator must return a single column. One way to rework this query is to remove the sum in the select list, and keep it only in the having clause:

SELECT student_name 
FROM   student 
WHERE  student_id IN (SELECT    t1.student_id
                      FROM      test t1
                      LEFT JOIN test t2 ON t1.Name = t2.Name AND 
                                           t1.Date < t2.Date AND
                                           t1.Score <= t2.Score
                      GROUP BY  t1.Name
                      HAVING    SUM(IF(t2.test_date IS NULL, 0, 1)) = 0 AND 
                                COUNT(*) > 1
                     )