Posted by & filed under Code Tips.

Needed a way to count how many quiz attempts students had on a specific quiz over a certain grade. The query below works with the Ad-Hoc database query Moodle plugin and lets you enter a module ID and what grade you want to count for a minimum.

SELECT u.lastname, u.firstname, u.idnumber,
(
	SELECT COUNT(*)
	FROM prefix_quiz_attempts attempts
	JOIN prefix_quiz quiz
	ON attempts.quiz=quiz.id
	JOIN prefix_course_modules cm
	ON cm.instance=quiz.id
	WHERE
	attempts.userid=u.id
	AND state='finished'
	AND cm.id=:module_id
	AND (attempts.sumgrades / quiz.sumgrades * quiz.grade) >= :minimum_grade
) AS quizcount
FROM prefix_user u
HAVING quizcount>0
ORDER BY u.lastname ASC, u.firstname ASC

Leave a Reply

Your email address will not be published. Required fields are marked *