Error: Mysql returns the following " Subquery return more than one row"
Good Morning! I created this query in mysql and when adding the 1st subquery that I called " totalEscolas "I get the following error:" Subquery return more than one row " without this subquery the code works blz.
Could you help me? Thank you!
SELECT qs.slot as Questao, avg(fraction) * 10 as Media, qs.quizid as Quiz, qs.questionid as QuestaoID, q.name as Descritor, SUBSTRING(u.`department`,2,4) as Nivel, SUBSTRING(u.`department`,6,1) as Turma, q.category as cat, quiz.name as Simulado,
(Select institution
from fhrw_user listaEscolasU
INNER JOIN fhrw_quiz_attempts listaEscolasQa ON listaEscolasQa.userid=listaEscolasU.id
INNER JOIN fhrw_quiz listaEscolasQ ON listaEscolasQ.id=listaEscolasQa.quiz
where listaEscolasQ.name=$P{simulado}
and listaEscolasU.lastname=lastname
and SUBSTRING(listaEscolasU.`department`,1,5) = $P{nivel}
and SUBSTRING(listaEscolasU.`department`,6,1) = $P{turma}
and listaEscolasQa.state="finished"
) as listaEscolas,
(Select count(totalAlunosQa.id)
from fhrw_quiz_attempts totalAlunosQa
INNER JOIN fhrw_user totalAlunosu ON totalAlunosu.id=totalAlunosQa.userid
INNER JOIN fhrw_quiz totalAlunosq ON totalAlunosq.id=totalAlunosQa.quiz
where totalAlunosq.name=$P{simulado}
and totalAlunosu.lastname=lastname
and SUBSTRING(totalAlunosu.`department`,1,5) = $P{nivel}
and SUBSTRING(totalAlunosu.`department`,6,1) = $P{turma}
and totalAlunosQa.state="finished"
) as totalAlunos,
(select avg(fraction) * 10 as mediaPT FROM fhrw_question_attempt_steps mediaqas
INNER JOIN fhrw_user mediau ON mediau.id=mediaqas.userid
INNER JOIN fhrw_question_attempts mediaqa ON mediaqa.id=mediaqas.questionattemptid
INNEr JOIN fhrw_question mediaq ON mediaq.id=mediaqa.questionid
INNER JOIN fhrw_quiz_slots mediaqs ON mediaqs.questionid=mediaqa.questionid
Inner Join fhrw_quiz mediaquiz ON mediaquiz.id=mediaqs.quizid
inner Join fhrw_school mediaschool ON mediaschool.IdEscola=mediau.lastname
where
SUBSTRING(mediau.`department`,1,5) = $P{nivel}
and SUBSTRING(mediau.`department`,6,1) = $P{turma}
and mediaq.category = 7
and mediaquiz.name=$P{simulado}
and mediau.lastname=lastname
and mediaqas.state!="todo"
and mediaqas.state!="complete") as mediaGeralPT,
(select avg(fraction) * 10 as mediaPT FROM fhrw_question_attempt_steps mediaqas
INNER JOIN fhrw_user mediau ON mediau.id=mediaqas.userid
INNER JOIN fhrw_question_attempts mediaqa ON mediaqa.id=mediaqas.questionattemptid
INNEr JOIN fhrw_question mediaq ON mediaq.id=mediaqa.questionid
INNER JOIN fhrw_quiz_slots mediaqs ON mediaqs.questionid=mediaqa.questionid
Inner Join fhrw_quiz mediaquiz ON mediaquiz.id=mediaqs.quizid
inner Join fhrw_school mediaschool ON mediaschool.IdEscola=mediau.lastname
where
SUBSTRING(mediau.`department`,1,5) = $P{nivel}
and SUBSTRING(mediau.`department`,6,1) = $P{turma}
and mediaq.category = 8
and mediaquiz.name=$P{simulado}
and mediau.lastname=lastname
and mediaqas.state!="todo"
and mediaqas.state!="complete") as mediaGeralMT,
(select avg(fraction) * 10 as mediaPT FROM fhrw_question_attempt_steps mediaqas
INNER JOIN fhrw_user mediau ON mediau.id=mediaqas.userid
INNER JOIN fhrw_question_attempts mediaqa ON mediaqa.id=mediaqas.questionattemptid
INNEr JOIN fhrw_question mediaq ON mediaq.id=mediaqa.questionid
INNER JOIN fhrw_quiz_slots mediaqs ON mediaqs.questionid=mediaqa.questionid
Inner Join fhrw_quiz mediaquiz ON mediaquiz.id=mediaqs.quizid
inner Join fhrw_school mediaschool ON mediaschool.IdEscola=mediau.lastname
where
SUBSTRING(mediau.`department`,1,5) = $P{nivel}
and SUBSTRING(mediau.`department`,6,1) = $P{turma}
and mediaquiz.name=$P{simulado}
and mediau.lastname=lastname
and mediaqas.state!="todo"
and mediaqas.state!="complete") as mediaGeral
FROM `fhrw_question_attempt_steps` qas
INNER JOIN fhrw_question_attempts qa ON qa.id=qas.`questionattemptid`
INNER JOIN fhrw_quiz_slots qs ON qs.questionid=qa.questionid
INNER JOIN fhrw_user u ON u.id = qas.userid
INNER JOIN fhrw_question q ON q.id=qa.questionid
Inner Join fhrw_quiz quiz ON quiz.id=qs.quizid
inner Join fhrw_school school ON school.IdEscola=u.lastname
where
quiz.name=$P{simulado}
and u.lastname=lastname
and SUBSTRING(u.`department`,1,5) = $P{nivel}
and SUBSTRING(u.`department`,6,1) = $P{turma}
and state!="todo"
and state!="complete"
group by qa.questionid
ORDER BY `Questao` ASC
3
Author: Miguel Silva, 2017-11-24
1 answers
Add a GROUP_CONTAT to return schools in just one row, your subquery is returning more than 1 row.
(Select
GROUP_CONCAT(institution SEPARATOR ',')
from fhrw_user totalEscolasU
INNER JOIN fhrw_quiz_attempts totalEscolasQa ON
totalEscolasQa.userid=totalEscolasU.id
INNER JOIN fhrw_quiz totalEscolasQ ON totalEscolasQ.id=totalEscolasQa.quiz
where totalEscolasQ.name=$P{simulado}
and totalEscolasU.lastname=lastname
and SUBSTRING(totalEscolasU.`department`,1,5) = $P{nivel}
and SUBSTRING(totalEscolasU.`department`,6,1) = $P{turma}
and totalEscolasQa.state="finished"
) as totalEscolas,
1
Author: arllondias, 2017-11-24 17:05:13