Dear All, could you help me to define a sql query to obtain a list of course with these columns?
I'm using the following query to obtain the first value, but how to add the second one (at least)?
SELECT c.fullname AS 'Course Name'
,COUNT(u.username) AS '# of Users'
FROM mdl_role_assignments AS r
JOIN mdl_user AS u on r.userid = u.id
JOIN mdl_role AS rn on r.roleid = rn.id
JOIN mdl_context AS ctx on r.contextid = ctx.id
JOIN mdl_course AS c on ctx.instanceid = c.id
WHERE rn.shortname = 'student'
GROUP BY c.fullname, rn.shortname
thank you