I am trying to extract student grades to generate grade reports for all students across our site with ~ 150 students.
Each teacher is reqired to have two categories in their gradebook: Assignments and Assessments. I want ot extract only those two category totals.
I am no SQL whiz, but put together the script below, which I am executing through the Ad-hoc Database queries plugin.
My problem is that I don't get just the category totals for these two categories. I seem to get every grade each student has in their gradebook, twice, even.
SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category', gc.fullname AS 'Grade Category',
CASE
WHEN gi.itemtype = 'Category'
THEN c.fullname
END AS 'Item Name', ROUND(gg.finalgrade,2) AS Percentage
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
JOIN prefix_grade_categories AS gc ON gc.courseid = c.id
WHERE (gi.courseid = c.id AND gc.fullname = 'Assessments')
OR (gi.courseid = c.id AND gc.fullname = 'Assignments')
ORDER BY `name` ASC
I'd be very grateful if someone can suggest how to improve my script.