mysql query showing error: #1241 - Operand should contain 1 column(s)

Question

Mysql query,

SELECT qcat.name,
COUNT( CASE WHEN qas.state = "todo" THEN 1 END ) AS gtotal,
COUNT( CASE WHEN qas.state = "gradedright" THEN 1 END ) AS rightanswer, 
COUNT( CASE WHEN qas.state = "gradedwrong" THEN 1 END ) AS wronganswer, 
SUM(qas.fraction) AS grade,
quiza.id 
FROM mdl_quiz_attempts quiza
JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid 
JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id 
JOIN mdl_question qstn ON ( qa.`questionid` = qstn.id ) 
JOIN mdl_question_categories qcat ON ( qstn.`category` = qcat.id ) 
WHERE quiza.id=1173 and FIND_IN_SET(qstn.id, (1,2,3,4,5,6)) GROUP BY quiza.id,qcat.name

showing error: #1241 - Operand should contain 1 column(s)

Try replace `FIND_IN_SET(qstn.id, (1,2,3,4,5,6))` with `FIND_IN_SET(qstn.id, 1,2,3,4,5,6)` (remove parenthesis) — Felippe Duarte, Jun 29 '18 at 17:31
not work showing , #1582 - Incorrect parameter count in the call to native function 'FIND_IN_SET' error — Jatin Grover, Jun 29 '18 at 17:33
My bad, Try replace `FIND_IN_SET(qstn.id, (1,2,3,4,5,6))` with `FIND_IN_SET(qstn.id, '1,2,3,4,5,6')` (list as string) — Felippe Duarte, Jun 29 '18 at 17:34
Well, there is where your error occurs. Now you need to write a query that return data. — Felippe Duarte, Jun 29 '18 at 17:40

score 0 · Answer 1 · answered Jun 29 '18 at 17:39

0

SELECT qcat.name,
COUNT( CASE WHEN qas.state = "todo" THEN 1 END ) AS gtotal,
COUNT( CASE WHEN qas.state = "gradedright" THEN 1 END ) AS rightanswer, 
COUNT( CASE WHEN qas.state = "gradedwrong" THEN 1 END ) AS wronganswer, 
SUM(qas.fraction) AS grade,
quiza.id 
FROM mdl_quiz_attempts quiza
JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid 
JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id 
JOIN mdl_question qstn ON ( qa.`questionid` = qstn.id ) 
JOIN mdl_question_categories qcat ON ( qstn.`category` = qcat.id ) 
WHERE quiza.id=1173 and FIND_IN_SET(qstn.id, "1,2,3,4,5,6") GROUP BY quiza.id,qcat.name

answered Jun 29 '18 at 17:39

Satya Krishna

84
5

Ithink you missed the position of the string like, " WHERE quiza.id=1173 and @@position_of_string = FIND_IN_SET(qstn.id, "1,2,3,4,5,6")" – Satya Krishna Jun 29 '18 at 17:44
WHERE quiza.id=1173 and 1 = FIND_IN_SET(qstn.id, "1,2,3,4,5,6") GROUP BY quiza.id,qcat.name – Satya Krishna Jun 29 '18 at 17:47
execute the query by removing the "and FIND_IN_SET(qstn.id, "1,2,3,4,5,6")" in where clause and check are you getting any data or not – Satya Krishna Jun 29 '18 at 18:00
@jatin-grover This answer appears to answer your specific question about the error. To continue troubleshooting. Try removing your filter context the `WHERE ...` statement and validate the results you expect exist. If not, you need to review your join statements. If they do exist try editing your where statements. – brianfeucht Jun 29 '18 at 18:01

score 0 · Answer 2 · answered Jun 29 '18 at 18:34

0

This is wrong:

FIND_IN_SET(qstn.id, (1,2,3,4,5,6))

It should be:

qstn.id IN (1,2,3,4,5,6)

or:

qstn.id BETWEEN 1 AND 6

FIND_IN_SET() is used when the list of values is in a comma-separated string, IN is used with a literal list in the query.

answered Jun 29 '18 at 18:34

Barmar

741,623
53
500
612

mysql query showing error: #1241 - Operand should contain 1 column(s)

2 Answers2