Hi there,
I have a report that successfully counts the number of students and teachers in all courses. How do I edit the last two subqueries in the select statement to count the group members instead of the course members?
select
-- get the category breadcrumb of the course
group_concat(distinct concat('<a target="_blank" href="%%WWWROOT%%/course/management.php?categoryid=',cat2.id,'">',cat2.name,'</a>') order by locate(concat('/', cat2.id, '/'), concat(cat1.path, '/')) separator ' > ') "full category path",
-- get the course's category name
concat('<a target="_blank" href="%%WWWROOT%%/course/management.php?categoryid=',cat1.id,'">',cat1.name,'</a>') "category name",
-- is this course visible to students?
case when cat1.visible = 1
then 'yes'
else 'no'
end "visible to students",
-- get course name
concat('<a target="_new" href="%%wwwroot%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') "diploma/certificate/unit name",
-- get group's name
g.name 'Group',
-- count the students and teachers in the course
-- N.B. this needs to change to 'count the students and teachers in each group in the course'.
(select count(1) from mdl_role_assignments ra_student where ct.id = ra_student.contextid and ra_student.roleid = 5) 'group\'s students',
(select count(1) from mdl_role_assignments ra_teacher where ct.id = ra_teacher.contextid and ra_teacher.roleid = 3) 'group\'s teachers'
from mdl_course_categories cat1
join mdl_course_categories cat2 on locate(concat('/', cat2.id, '/'), concat(cat1.path, '/')) > 0-- and cat1.path like '/15%'
join mdl_course c on c.category = cat1.id
join mdl_context ct on c.id = ct.instanceid and ct.contextlevel = 50
left join mdl_groups g on c.id = g.courseid
group by
cat1.depth,
cat1.path,
cat1.name,
cat1.coursecount,
cat1.visible,
c.fullname,
c.id,
g.id
order by c.id
select
count(distinct members.userid) students
from mdl_course c
join mdl_context cx on c.id = cx.instanceid
and cx.contextlevel = 50
join mdl_role_assignments ra on cx.id = ra.contextid
join mdl_role r on ra.roleid = r.id
and r.shortname = 'student' -- replace with the role required to report on.
left join mdl_groups g on c.id = g.courseid - a course may not have any groups
left join mdl_groups_members members on g.id = members.groupid -- there may be no members in a group
join mdl_course_categories course_cat on course_cat.id = c.category
group by members.groupid
order by
c.id asc,
g.name asc
Your help is greatly appreciated