I found the contributed students by course report didn't always report the correct role[*] and wasn't very useful for my site, so I wrote two new ones. The first is total students for a course. The second only shows current students (in other words it removes students whose enddate on the enrollment is in the past). Both of them eliminate non-visible courses as we have a number of them used for templates and so on, and we don't want to clutter the report. At the end of a course we'll plan on hiding it, and next trimester we don't want them showing up in the report either.
The main difference with the contributed one is that it start with role_assignments, not user_enrollments. The reason is that user_enrollments doesn't have the actual role. It has a like to an enrol record for the plugin instance which shows the current default role, but that could have changed, or could be overridden depending on the plugin.
Caveat: it's certainly possible that I don't fully understand the structure of some of these tables. In particular if a student was enrolled in a via more than one enrollment plugin, with overlapping dates, it probably double counts that student.
We use the enrol_apply plugin for enrollment, and it seems to work fine with that.
Anyone is welcome to use these:
Total students in a course (uses role assignments, not the user enrollments table, so I think it's more accurate)
select count(ra.userid) as "Total Students", c.fullname as "Course Name" from role_assignments ra, prefix_context ctx, prefix_course c
where ra.contextid = ctx.id
and ctx.contextlevel = 50
and c.id = ctx.instanceid
and ra.roleid = 5
and c.visible=1
group by ctx.id
Current students in a course (links back to the user_enrollment, which as far as I can see is the only place with the date range for the enrollment):
select count(ra.userid) as "Current Students", c.fullname as "Course Name"
from role_assignments ra, prefix_context ctx, prefix_course c, prefix_enrol e,
prefix_user_enrolments ue
where ra.contextid = ctx.id
and ctx.contextlevel = 50
and c.id = ctx.instanceid
and ra.roleid = 5
and c.visible=1
and ue.userid = ra.userid
and ue.enrolid = e.id
and e.courseid = c.id
and (ue.timeend = 0 or ue.timeend > unix_timestamp())
group by c.id
order by c.fullname
Feel free to give any comments, suggestions, or if it doesn't work properly with your configuration I will try to improve or fix it.