Good morning! I created this query in mysql and when I add the 1st subquery 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? Thanks!
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