quiz score
SELECT
mdl_grade_items.itemname,
mdl_grade_items.grademax,
ROUND(mdl_grade_grades.finalgrade, 0) AS finalgrade,
mdl_user.firstname,
mdl_user.lastname,
mdl_user.username
FROM
mdl_grade_grades
INNER JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
INNER JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
WHERE (mdl_grade_items.itemname IS NOT NULL)
AND (mdl_grade_items.itemtype = 'mod' OR mdl_grade_items.itemtype = 'manual')
AND (mdl_grade_items.itemmodule = 'quiz' OR mdl_grade_items.itemmodule IS NULL)
AND (mdl_grade_grades.timemodified IS NOT NULL)
AND (mdl_grade_grades.finalgrade > 0)
AND (mdl_user.deleted = 0)
SELECT MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) AS calendar_month, YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) AS calendar_year, mdl_role.name AS user_role, COUNT( DISTINCT mdl_stats_user_monthly.userid ) AS total_users
FROM mdl_stats_user_monthly
INNER JOIN mdl_role_assignments ON mdl_stats_user_monthly.userid = mdl_role_assignments.userid
INNER JOIN mdl_context ON mdl_role_assignments.contextid = mdl_context.id
INNER JOIN mdl_role ON mdl_role_assignments.roleid = mdl_role.id
WHERE mdl_context.contextlevel =50
AND `mdl_stats_user_monthly`.`stattype` = 'activity'
AND mdl_stats_user_monthly.courseid <>1
GROUP BY MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , mdl_stats_user_monthly.stattype, mdl_role.name
ORDER BY YEAR( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , MONTH( from_unixtime( `mdl_stats_user_monthly`.`timeend` ) ) , mdl_role.name
LIMIT 0 , 30
SELECT
(SELECT COUNT(id) FROM mdl_course) - 1 AS courses,
(SELECT COUNT(id) FROM mdl_user WHERE deleted = 0 AND confirmed = 1) AS users,
(SELECT COUNT(DISTINCT ra.userid)
FROM mdl_role_capabilities rc
JOIN mdl_role_assignments ra ON ra.roleid = rc.roleid
WHERE rc.capability IN ('moodle/course:upd' || 'ate', 'moodle/site:doanything')) AS teachers,
(SELECT COUNT(id) FROM mdl_role_assignments) AS enrolments,
(SELECT COUNT(id) FROM mdl_forum_posts) AS forum_posts,
(SELECT COUNT(id) FROM mdl_resource) AS resources,
(SELECT COUNT(id) FROM mdl_question) AS questions
list all grades
SELECT * FROM
(SELECT
gg.timemodified,
u.username,
u.firstname,
u.lastname,
u.email,
gg.finalgrade,
gi.courseid
FROM mdl_grade_grades_history gg
JOIN mdl_grade_items gi ON gi.id = gg.itemid
JOIN mdl_user u ON u.id = gg.userid
JOIN mdl_course c ON c.id=gi.courseid
WHERE
gi.itemtype = 'course' AND
gg.finalgrade IS NOT NULL AND
(gg.timemodified > 1 AND gg.timemodified < 99999999999) AND c.category=76
ORDER BY gg.timemodified DESC) AS temp
GROUP BY username
ORDER BY username
