Gradebook

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

Hi,

I'm trying to type up a SQL to get a list with users which completed a course, incl. all final course grades (if available), not matter if the user is still enrolled or unenrolled in the course.

That's what I have so far:

SELECT DISTINCT u.username, u.id, u.lastname, u.firstname, c.fullname, ROUND(gg.finalgrade, 0) FROM m_grade_grades_history gg, m_grade_items gi, m_course c,
m_course_completions cc, m_user u
WHERE gg.userid = u.id AND gi.id = gg.itemid AND gi.itemtype = 'course' AND c.id = gi.courseid
AND cc.userid = u.id AND CC.TIMECOMPLETED > 0
ORDER BY c.fullname, u.username;

 

My problem is that some users appear more than once in the result set:

student01    101    Wilman (test01)    Susan    Fire Extinguisher Training    50
student01    101    Wilman (test01)    Susan    Fire Extinguisher Training    75
student01    101    Wilman (test01)    Susan    Fire Extinguisher Training    100
student01    101    Wilman (test01)    Susan    Fire Extinguisher Training   

 

Why does that happen and how can I get the correct, final grade?

I do not want to include a "WHERE finalgrade > 0" because some of our courses don't have final grades. But I want these ungraded user to appear in the list as well, once they completed the course.

 

Thanks

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

Hi Debbie,

Thanks for your reply.

Even with this code I run into the same issue. I replaced prefix_grades_grade by prefix_grades_grade_history so that I can get the grades from the unenrolled users too. But that results in having multiple entries with different grades for the user.

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

Does anybody have any idea on this?

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
Group Particularly helpful Moodlers

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.