Hi there!
How can I get all the roles of users in course groups? I've tried using this query, but it doesn't count the `groupstudentcount` and `groupspnetcount` columns correctly.
select
cat.name category,
c.fullname course,
g.name groupname,
count(distinct gm.userid) groupmembercount,
case when ra.roleid = 5
then count(distinct ra.userid)
else 0
end as groupstudentcount,
case when ra.roleid = 9
then count(distinct ra.userid)
else 0
end as groupspnetcount
from mdl_course_categories cat
join mdl_course c on cat.id = c.category
join mdl_context ct on c.id = ct.instanceid
and ct.contextlevel = 50
left join mdl_groups g on g.courseid = c.id
left join mdl_groups_members gm on g.id = gm.groupid
left join mdl_role_assignments ra on gm.userid = ra.userid
and ra.contextid = ct.id
group by
cat.id,
c.id,
g.id
order by
cat.name asc,
c.fullname asc,
g.name asc
Here's the fiddle containing an export of each required table from phpmyadmin: link. Thanks for your help in advance!