Error: Mysql returns the following "Subquery Return more than one Row"

Question

Good morning! I created this query in mysql and by adding 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? 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

Answer 1

Add a GROUP_CONTAT to return schools in just one line, your subquery is returning more than 1 line.

(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,