>>>>>>>>>>>>>>> también tengo este query, que muestra la cantidad de foros en un curso, la cantidad de participaciones en el foro, la cantidad de participaciones revisadas por el docente y la cantidad de participaciones hecha por el docente
SELECT a.shortname, a.fullname, a.iteminstance, a.itemname, a.calificados, b.postparticipacion, c.alumnospartiparon,d.docenteparticipacion, e.totalalumnos
FROM (
SELECT c.shortname, c.fullname ,gi.iteminstance,gi.itemname , count(distinct(gg.userid)) as calificados
FROM mdl_grade_items gi
left join mdl_grade_grades gg on gi.id = gg.itemid
left join mdl_course c on gi.courseid = c.id
where c.category = '197' and gi.itemmodule = 'forum'
and gg.finalgrade > 0
group by c.fullname ,gi.itemname ) a
Left join
(
select
c.shortname, f.id, f.name, count((p.userid)) as postparticipacion
from mdl_forum f
left join mdl_forum_discussions fd on f.id = fd.forum
left join mdl_forum_posts p on p.discussion = fd.id
left join mdl_course c on c.id = f.course
where c.category = '197'
group by c.shortname, f.name
) b on a.shortname = b.shortname and a.iteminstance = b.id
left join
(
select
c.shortname, f.id, f.name, count(distinct(p.userid)) as alumnospartiparon
from mdl_forum f
left join mdl_forum_discussions fd on f.id = fd.forum
left join mdl_forum_posts p on p.discussion = fd.id
left join mdl_course c on c.id = f.course
where c.category = '197'
group by c.shortname, f.name
)c on a.shortname = c.shortname and a.iteminstance = c.id
left join
(
select
c.shortname, f.id, f.name, count((p.userid)) as docenteparticipacion
from mdl_forum f
left join mdl_forum_discussions fd on f.id = fd.forum
left join mdl_forum_posts p on p.discussion = fd.id
left join mdl_user u on u.id = p.userid
left JOIN mdl_role_assignments ra ON ra.userid = u.id
left JOIN mdl_context ct ON ct.id = ra.contextid
left join mdl_course c on c.id = f.course and c.id = ct.instanceid
left JOIN mdl_role r ON r.id = ra.roleid
where c.category = '197' and r.id = 5
group by c.shortname, f.name
) d on a.shortname = d.shortname and a.iteminstance = d.id
left join
(
select c.shortname , count(u.username) as totalalumnos
FROM mdl_user u
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid
INNER JOIN mdl_course c ON c.id = ct.instanceid
INNER JOIN mdl_role r ON r.id = ra.roleid
WHERE r.id = 9 and c.category = '197'
group by c.shortname
) e on a.shortname = e.shortname
group by a.shortname, a.fullname, a.itemname