mdl_quiz_slots connects them...
Thank you,
I still can't create report that I need.
currently I have two reports:
1) Total Number of Questions in each quiz in the courses
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course,'">',c.shortname,'</a>') AS CourseCode
,c.category As College
,qz.id
,count(qs.questionid) AS CountQuestions
from prefix_quiz qz
JOIN prefix_course c ON qz.course=c.id
JOIN prefix_quiz_slots qs ON qz.id=qs.quizid
JOIN prefix_question q ON qs.questionid=q.id
GROUP By course
ORDER BY c.category,CountQuestions DESC
2)Total attempts in each quiz in each course
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course,'">',c.shortname,'</a>') AS CourseCode
,c.category As College
,qz.id QuizID
,count(qa.quiz) AS Attempts
from prefix_quiz qz
JOIN prefix_course c ON qz.course=c.id
JOIN prefix_quiz_attempts qa ON qz.id=qa.quiz
GROUP By course,qz.id
ORDER BY c.category
but I need one report that provides the number of questions and attempts (responses) including the zero results.
also, the number of rows in the above reports are different
Attaching an SQL query created by my colleague Kobi Yechimovich , that should help you.
SELECT
#quiza.id as quiz_attemptid ,
#qa_this.slot as this_slot,
#qa_this.questionid as this_questionid ,
answers_prev.answer
FROM
mdl_question_attempts qa_this
JOIN mdl_quiz_slots slot_this ON qa_this.questionid = slot_this.questionid
JOIN mdl_question_attempts qa_prev ON qa_this.slot = qa_prev.slot + 1
JOIN mdl_quiz_slots slot_prev ON qa_prev.questionid = slot_prev.questionid
JOIN mdl_quiz_attempts quiza ON quiza.quiz = slot_prev.quizid
LEFT JOIN mdl_question_attempt_steps qas_order ON qas_order.questionattemptid = qa_prev.id AND qas_order.state ='todo'
LEFT JOIN mdl_question_attempt_step_data qasd_order ON qasd_order.attemptstepid = qas_order.id AND qasd_order.name = '_order'
LEFT JOIN mdl_question_attempt_steps qas_answer ON qas_answer.questionattemptid = qa_prev.id AND qas_answer.state ='complete'
AND qas_answer.userid= qas_order.userid
LEFT JOIN mdl_question_attempt_step_data qasd_answer ON qasd_answer.attemptstepid = qas_answer.id AND qasd_answer.name = 'answer'
LEFT JOIN mdl_question_answers answers_prev on answers_prev.id = SUBSTRING_INDEX( SUBSTRING_INDEX( qasd_order.value, ',', qasd_answer.value + 1 ), ',', -1 )
WHERE
quiza.id = 202 # quiz_attemptid
AND qas_order.userid = 6 # userid
AND qa_this.slot = 2 # current slot or use the next line if you know the questionid
#AND qa_this.questionid = 339 # questionid
ORDER BY qas_answer.timecreated DESC LIMIT 1
Hope it helps.
Thank you for your help and the report, but this report didn't work with me
==
I wrote 2 sqls that do what I need, I hope some one can help me to merge them, currently I do that in excel.
1) Total Questions in each quiz in the courses
select
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS Course
,c.category
,quizid
,count(qs.quizid) As TotalQuestions
From prefix_quiz_slots qs
JOIN prefix_quiz q ON q.id=qs.quizid
JOIN prefix_course c ON c.id=q.course
GROUP BY qs.quizid
ORDER BY c.category,q.course
2) Total number of Attepts in each quiz in all courses
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course,'">',c.shortname,'</a>') AS CourseCode
,c.category As College
,qz.id QuizID
,count(qa.quiz) AS Attempts
from prefix_quiz qz
JOIN prefix_course c ON qz.course=c.id
JOIN prefix_quiz_attempts qa ON qz.id=qa.quiz
GROUP By course,qz.id
ORDER BY c.category