Databases: SQL query Get course final grade based on Groups

Databases: SQL query Get course final grade based on Groups

by Ebin Manuval -
Number of replies: 1

From a specific course (say id =3), I need to get the course final grade of all students based on Groups. Some groups don't have any students , so i need  to exclude them also. How can query that 

Average of ratings: -
In reply to Ebin Manuval

Re: Databases: SQL query Get course final grade based on Groups

by Darko Miletić -

This should do it

    SELECT 
           gg.id, gi.courseid, gr.id AS groupid, gg.userid, gg.finalgrade
      FROM mdl_grade_grades gg
INNER JOIN mdl_user u ON u.id = gg.userid AND u.deleted = 0
INNER JOIN mdl_grade_items gi ON gi.id = gg.itemid
INNER JOIN mdl_course c ON c.id = gi.courseid
 LEFT JOIN mdl_groups gr ON gr.courseid = gi.courseid
 LEFT JOIN mdl_groups_members gm ON gm.groupid = gr.id
     WHERE gg.finalgrade IS NOT NULL
           AND
           gi.itemtype = 'course'
  GROUP BY gi.courseid, gr.id, gg.userid


Average of ratings: -