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

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

by Michael E -
Number of replies: 8
Picture of Core developers Picture of 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

In reply to Michael E

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

by Debbie Unterseher -

Ran across this website today.  Maybe this will help.

http://docs.moodle.org/24/en/ad-hoc_contributed_reports#Site-Wide_Grade_Report_with_Just_Course_Totals

 

In reply to Debbie Unterseher

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

by Michael E -
Picture of Core developers Picture of 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.

In reply to Michael E

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

by Michael E -
Picture of Core developers Picture of Testers

Does anybody have any idea on this?

In reply to Michael E

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

by Constance Horne -

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_

In reply to Constance Horne

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

by Michael E -
Picture of Core developers Picture of 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?

 

In reply to Constance Horne

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

by Robert Russo -

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.

In reply to Robert Russo

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

by Michael E -
Picture of Core developers Picture of Testers

Thank you, Robert.

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