I need to calculate the average grade(gg.finalgrade) per assignment(a.name) in Moodle using a subquery(highlighted). I cannot figure this out

SELECT 
u.username, 
u.firstname,
u.lastname,
u.id,
c.fullname AS "Course",
a.name,
IFNULL(ROUND(gg.finalgrade / gg.rawgrademax * 100 ,0),0) as 'Percentage',

**(
    SELECT SUM(gg.finalgrade) / a.name
    FROM prefix_assign a
    JOIN prefix_course c ON c.id = a.course
    JOIN prefix_grade_items gi on gi.courseid = c.id
    JOIN prefix_grade_grades gg ON gg.itemid = gi.id
    JOIN prefix_user u ON u.id = gg.userid
) AS 'Avg'**



FROM prefix_user u
JOIN prefix_role_assignments ra ON ra.userid = u.id
JOIN prefix_context ctx ON ctx.id = ra.contextid
JOIN prefix_course c ON c.id = ctx.instanceid
JOIN prefix_course_modules cm ON cm.course = c.id
JOIN prefix_modules m ON m.id = cm.module AND m.name = 'assign'
JOIN prefix_assign a ON a.id = cm.instance AND a.course = c.id

JOIN prefix_grade_items gi on gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = a.id
LEFT JOIN prefix_grade_grades gg ON gg.itemid = gi.id AND gg.userid = u.id

GROUP BY a.id, u.id

CodePudding user response：

try use OVER clause:

SELECT SUM(gg.finalgrade) OVER(partition by a.name)/ count (*) OVER (partition by a.name)