Medium in Mysql database

Question

People I have the following scenario in the database:

Tables:

mdl_question_attempts (slot, questionid, rightanswer, responsesummary)

mdl_quiz_slots (slot, quizid, questionid)

On the table mdl_question_attempts attempts to respond to a particular quiz are recorded. questionid is the id of the issue, rightanswer is the correct answer, responsesummary is the answer given by the student.

On the table mdl_quiz_slots the questions that were applied to the students are recorded, where questionid is the id of the issue, quizid is the id of the questionnaire (set of questions).

I would like to have the average hit for a certain quiz.

Answer 1

Taking into account that you just want to make the calculation described in the question (the average of hits per question), so I believe the table mdl_quiz_slots no need to enter the query. The query below returns:

• id_question: id of the issue
• total_answers: number of answers to each question
• hits: number of hits
• media_acertos: percentage of hits in relation to total answers to each question

Query:

select distinct(questionid) as id_questao,
count(questionid) as total_respostas,
sum(
   case when responsesummary = rightanswer
   then 1 else 0
   end
) as acertos,
concat(
   round(
      sum(
         case when responsesummary = rightanswer
         then 1 else 0
         end
      )
      / count(responsesummary) * 100
   ),'%'
) as media_acertos
from mdl_question_attempts
group by questionid