Course Enrollment Method Report

Course Enrollment Method Report

by Doug Swenson -
Number of replies: 2

I am looking for some help with the SQL code to produce a report that shows the number of enrollments per enrollment method in each course. I am currently using the Course Enrollment report from the repository and would just like to add the columns for Manual Enrollments, Self Enrollment and Paypal Enrollments.

 

Here is what I am using right now:

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course.id,'">',course.fullname,'</a>') AS Course
,concat('<a target="_new" href="%%WWWROOT%%/user/index.php?contextid=',context.id,'">Show users</a>') AS Users
, COUNT(course.id) AS Students
FROM prefix_role_assignments AS asg
JOIN prefix_context AS context ON asg.contextid = context.id AND context.contextlevel = 50
JOIN prefix_user AS USER ON USER.id = asg.userid
JOIN prefix_course AS course ON context.instanceid = course.id
WHERE asg.roleid = 5
GROUP BY course.id
ORDER BY COUNT(course.id) DESC

Can anyone assist or get me started in the right direction?

Average of ratings: -
In reply to Doug Swenson

Re: Course Enrollment Method Report

by Kevin Cooke -

The following will show a total count for enrollment methods in all courses.

SELECT c.fullname AS 'Course',
       e.enrol AS 'Method',
       Count(e.enrol) AS 'Count'
  FROM {user_enrolments} AS ue
    JOIN {enrol} AS e ON e.id = ue.enrolid
    JOIN {course} AS c ON c.id = e.courseid
  GROUP BY c.fullname, e.enrol
Average of ratings: Useful (2)
In reply to Kevin Cooke

Re: Course Enrollment Method Report

by Doug Swenson -

Kevin,

 

Thanks so much! This is the info I needed and it works perfectly. You are a life saver.

 

Doug

Average of ratings: -