Hello,
I am working on a sql query that shows me the numbers of students in a group, as well the groupsname,course id, coursefullname and groupsname.
Sofar - based on the database - I get this query running. Though I have some issues with it.
- For some reason it also shows the total number of students in a group counted on all course.
- When I try to place it in Configurable report it will not work due to "colum mdl_course_categories.name" is invalid in the select list because it not contained in either an agregate function or the group by clause.
To be fair... I think I am causing conflicts at one point. Though I am working on this for about 2hours now and this is the closes I got.
Is there anyone who can help me out, or has some tips? By the way I am working on Moodle 3.0
Thank
Gemma
====
SELECT DISTINCT
course_cat.name as categorie,
c.idnumber AS cursuscode,
c.fullname AS cursus,
g.name As groepnaam
,count(members.groupid) as students
FROM `mdl_course` AS c
inner JOIN mdl_context AS cx ON c.id = cx.instanceid AND cx.contextlevel = '50'
inner jOIN mdl_role_assignments AS ra ON cx.id = ra.contextid
INNER JOIN mdl_role AS r ON ra.roleid = r.id
left JOIN mdl_user AS u ON ra.userid = u.id
left JOIN mdl_groups AS g ON c.id = g.courseid
left JOIN mdl_groups_members AS members ON g.id = members.groupid
left JOIN mdl_course_categories as course_cat on course_cat.id = c.category
where ra.roleid = 5 and course_cat.name like '%2016%'
Group by u.id