Average on the database in MySQL

Question

Average on the database in MySQL

Navigation

#1 by (1 votes)

2

Personal I have the following scenario in the database:

Tables:

mdl_question_attempts (slot, questionid, rightanswer, responsesummary)

mdl_quiz_slots (slot, quizid, questionid)

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

In the mdl_quiz_slots table, the questions that were applied to the students are recorded, where questionid is the id of the question, quizid is the id of the questionnaire. >

I would like to have the correct score for a particular quiz.

mysql

asked by anonymous 03.11.2017 / 23:15

1 answer

Canvas increasing the animation speed at each sprite exchange Python - invalid literal for float ()

score 1 · Accepted Answer

Given that you only want to do the calculation described in question ( average hits per question ), I soon believe that the mdl_quiz_slots table is not required to enter the query. The query below returns:

question_id: id of the question
total_replies: number of answers for each question
hits: number of hits
media_acertos: percentage of hits in relation to the total answers of 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