Hi all,
So i've gone and modified one of the queries from the repository so that i have a record of every students attendance activity result returned in a report table.
Fields returned are:
As our modules are split in to multiple moodle courses, each person has multiple rows with a different module name and the percentage attended next to it. How would i then be able to take that result set and return a total attendance for each student using an average of their module attendance percentages? something like:
I understand i should probably be using a subquery but i'm a bit stumped as this would be my first jump in to that level of sql.
Custom SQL Query is the following:
SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Module', cc.name AS 'Course',
gi.itemname AS 'Item Name', ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage_attended,
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') AS Acceptable
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
WHERE gi.courseid = c.id AND gi.itemmodule = 'attendance'
ORDER BY Name ASC, Surname ASC