I'm new to Moodle and MySQL and hoping I can get some help with a query I've written. I'm looking to create a report to show all the assignments per unit, and for each one the percentage of students who received a pass, merit or distinction as well as the percentage of students who have needed to resubmit.
I've written the below, and it is showing the assignment names, pass, merit and distinction percentages. However, it's not showing the correct unit IDs and the resubmission percentage is always showing as 0 which isn't correct as some students have needed to resubmit.
Can someone maybe give me some insight into where I'm going wrong?
SELECT
gi.courseid AS unit_ID,
a.name AS assignment_name,
COUNT(DISTINCT s.id) AS total_submissions,
LEAST(100, ROUND((SUM(CASE WHEN gg.finalgrade = 3.000 THEN 1 ELSE 0 END) / COUNT(DISTINCT s.id) * 100))) AS pass_percentage,
LEAST(100, ROUND((SUM(CASE WHEN gg.finalgrade = 4.000 THEN 1 ELSE 0 END) / COUNT(DISTINCT s.id) * 100))) AS merit_percentage,
LEAST(100, ROUND((SUM(CASE WHEN gg.finalgrade = 5.000 THEN 1 ELSE 0 END) / COUNT(DISTINCT s.id) * 100))) AS distinction_percentage,
LEAST(100, ROUND((SUM(CASE WHEN g.attemptnumber = 2 THEN 1 ELSE 0 END) / COUNT(DISTINCT s.id) * 100))) AS resubmission_percentage
FROM {assign} AS a
LEFT JOIN {assign_submission} AS s ON a.id = s.assignment
LEFT JOIN {assign_grades} AS g ON s.id = g.assignment
JOIN {grade_grades} AS gg ON s.userid = gg.userid
INNER JOIN {grade_items} AS gi ON gg.itemid = gi.id
GROUP BY gi.courseid, a.name
