Gradebook

SQL to get all final course grades from enrolled and unenrolled users

 
 
Picture of Constance Horne
Re: SQL to get all final course grades from enrolled and unenrolled users
 

I finally figured out a query that appears to work on my Unix MySQL 2.4 database:

SELECT u.firstname , u.lastname , u.email
, cc.name CourseName,
ROUND(gg.finalgrade,2) Grade,
FROM_UNIXTIME(gi.timemodified) TimeModified
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_grade_grades AS gg ON gg.userid = u.id
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemtype = 'course'

You would need to change your database prefix if it is not mdl_

Picture of Michael E
Re: SQL to get all final course grades from enrolled and unenrolled users
Group Testers

Thanks, Constance. This script returns all grades for currently enrolled users - that's the part I already have. I now need to get all grades for already unenrolled users (which have been in the course at one point and completed it). I do not want to enroll them again. Any ideas on that?

 

Picture of Robert Russo
Re: SQL to get all final course grades from enrolled and unenrolled users
 

You can ignore all the context stuff and

SELECT c.shortname AS shortname, CONCAT(mu.firstname, ' ', mu.lastname) AS fullname, gg.finalgrade AS finalgrade
FROM mdl_grade_items AS gi
INNER JOIN mdl_course c ON c.id = gi.courseid
LEFT JOIN mdl_grade_grades AS gg ON gg.itemid = gi.id
INNER JOIN mdl_user AS mu ON gg.userid = mu.id
WHERE gi.itemtype = 'course'
ORDER BY c.id asc

That should solve it.

Picture of Michael E
Re: SQL to get all final course grades from enrolled and unenrolled users
Group Testers

Thank you, Robert.

This actually brings back all final grades from currently enrolled users - but not the grades from already unenrolled users.

Picture of LeelaVinodh Ramachandran
Re: SQL to get all final course grades from enrolled and unenrolled users
 

Hi,

Even I am looking for the same. Please update if you have any solution.