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

(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