Error #1241 Operand should contain 1 column(s) in Mysqlworkbench

Question

I just try following query:

CREATE DEFINER=`root`@`localhost` FUNCTION `cal1`(z DATE , y DATE) RETURNS float
BEGIN
SET @c= (SELECT a.StudentName, (b.marks/a.marks) as difference from (select Date, StudentName, marks from studenthistory2015 WHERE Date=z) as a INNER JOIN (select Date, StudentName, marks from studenthistory2015 where Date=y) as b on a.date=z and b.date=y WHERE a.date = z and b.date = y and a.StudentName=b.StudentName
);
RETURN @c;
END

Execution :

select student_history.cal1('2015-01-01', '2015-02-01'); But after execution, it shows Error #1241 Operand should contain 1 column(s)

How should I resolve this query?

Answer 1

As P.Salmon said, you are trying to return a set of params, containing both strings and floats (i assume)

SELECT a.StudentName, (b.marks/a.marks) as difference

instead of returning a single variable, although you actually did declare, in your function, that you will return a float: RETURNS float

Maybe you can convert you function into a procedure, like mentioned in this detailed answer here, answering another question related to yours.