Same answers getting displayed more than once in SQL table

Same answers getting displayed more than once in SQL table

by Ishan Gupta -
Number of replies: 2

I found this really helpful query by Tim Hunt

SELECT upper(u.username), q.name as question, qa.rightanswer, qa.responsesummary FROM mdl_quiz_attempts quiza JOIN mdl_question_usages qu ON qu.id = quiza.uniqueid JOIN mdl_question_attempts qa ON qa.questionusageid = qu.id JOIN mdl_question q ON qa.questionid = q.id JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id AND qas.sequencenumber = ( SELECT MAX(sequencenumber) FROM mdl_question_attempt_steps WHERE questionattemptid = qa.id ) JOIN mdl_user u ON qas.userid = u.id LEFT JOIN mdl_question_attempt_step_data qasd ON qasd.attemptstepid = qas.id

Now I am storing the results in a table tabl1. I am using a second query in the form

select count(*),question,responsesummary from tabl1 group by question,responsesummary

Now as per the query I should get count to all the responses to a question. But options are coming twice. I wonder if they are internally different because they appear the same. I have added a screenshot for reference below. Ideally only 4 options should be present (Questions are MCQ) . It's happening for many questions. Any idea why is this happening? Maybe the HTML formatting or something.Same option getting repeated

Average of ratings: -
In reply to Ishan Gupta

Re: Same answers getting displayed more than once in SQL table

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Have you looked closely at the output of the first query. You seem to be making assumptions about the results it gives that are not true.

Do you have a good reason for doing this in SQL. If you go to Quiz adminstration -> Results -> Statistics in Moodle, there is a nice report that tells you have often each choice has been selected, and there are options to download that report in a range of formats.

Average of ratings: -
In reply to Tim Hunt

Re: Same answers getting displayed more than once in SQL table

by Ishan Gupta -

Hi Tim, 

Yes I need SQL. I need to examine every question like what kind of responses the questions are getting. Even for multiple choice which options are being clicked more and all. So I was simply counting the no. responses to a particular question choice.

Average of ratings: -