SQL report showing total number of correct answers for each question

SQL report showing total number of correct answers for each question

by David North -
Number of replies: 7

I am using moodle 2.8.1

From what I can see, there is no report in core moodle which displays the total number of learners who answered the questions and the total number of learners who got the question correct/incorrect. The closest thing to it is the Quiz Admin -> Results -> Responses report. But this, when downloaded and on the browser, shows the questions randomised. It is then very manually intensive to sort the questions to get a table similar to this below, including an extra column for 'Question Text':



I've now been looking into writing a SQL statement that will produce a table like this but I'm struggling to understand the quiz and questions data structure. This https://docs.moodle.org/dev/Quiz_database_structure documentation seems slightly outdated. 

Could someone help me with this problem by either helping me write a SQL query for this or explaining which database tables and PK's/FK's link up to eachother for me to get this information.


Thanks.

Average of ratings: -
In reply to David North

Re: SQL report showing total number of correct answers for each question

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

Quiz Admin -> Results -> Statistics

Average of ratings: -
In reply to Tim Hunt

Re: SQL report showing total number of correct answers for each question

by David North -

Thanks Tim

The statistics report doesn't seem to show the question name, because it is set to random all the question names are displayed as Random(CategoryName). Or maybe its got to do with the way the quiz was set up.


I'm looking into it now. I may just write a short SQL query. Thanks to the question_statistics table, it shouldn't take me too long to get what I need.

Average of ratings: -
In reply to David North

Re: SQL report showing total number of correct answers for each question

by Peggy Kearney -

Hi David,

I too don't have a problem getting the statistics--my quiz questions are also displayed as a Random Category Name and the specific question name is not present. Did you find an answer to this question??

Average of ratings: -
In reply to Peggy Kearney

Re: SQL report showing total number of correct answers for each question

by David North -
Unfortunately we haven't found a simple way of doing this. Yeah, when the question is set to random, the question name is displayed as Random Category. We just had to manually input the question names in excel.
Average of ratings: -
In reply to Peggy Kearney

Re: SQL report showing total number of correct answers for each question

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

It might be easier to understand what you are asking if you attach a screen grab of the statistics report.

I aslo wonder if your problem is related to this bug: MDL-48092.

Average of ratings: -
In reply to Tim Hunt

Re: SQL report showing total number of correct answers for each question

by David North -

Hi Tim

I think it's similar to that bug but not exactly the same.

The issue is, as shown in the image, the question names are displayed as "Random ( CategoryName )" when the questions are set to random. Therefore the statistics are pretty meaningless as you don't know which question is being referred to.




It may be that it's set to group and display the stats for the questions positions, rather than for each question, and that is why it is like this? But that wouldn't make much sense as the question position is irrelevant information to the teacher.

Average of ratings: -
In reply to David North

Re: SQL report showing total number of correct answers for each question

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

I know that the statistics by postion are not very helpful in this case, but still, we always display them for the sake of consitency.

We do also display the break-down analysed by which question each student saw. However, because all these random questions are the same, we only show the detailed break-down once, and I think that comes at the end.

So, try scrolling down to the end of the report. (At which point you will probably see something that suffers from bug MDL-48092 wink)

(If you know PHP code, and want to see the bits that control this, I think it is core_question\statistics\questions\calculator::calculate and core_question\statistics\questions\all_calculated_for_qubaid_condition::all_subq_and_variant_stats_for_slot.)

Average of ratings: -