Discrepancies between SQL query and Grader report enrolments and completions

Discrepancies between SQL query and Grader report enrolments and completions

by Derek Chaplin -
Number of replies: 2

I've been tasked with creating a report for admin that determines the number of people enrolled in a course and the number who have completed the course, on a per course level.

The following SQL code returns results but there seems to be a discrepancy between the numbers listed by the query and the numbers of users displayed in the grader report  or those listed in the enrolled users.

SELECT c.fullname, COUNT(DISTINCT ue.id) AS Enroled,
COUNT(DISTINCT p.timecompleted) AS TotalCompletions
FROM prefix_course AS c 
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_course_completions AS p on p.course = c.id
GROUP BY c.id
ORDER BY c.fullname

Any ideas as to why this is happening?

Average of ratings: -
In reply to Derek Chaplin

Re: Discrepancies between SQL query and Grader report enrolments and completions

by Derek Chaplin -

I figured our why there was a discrepancy. Some enrolled users had dual roles (i.e. student and manager).

Now I just have to figure out the SQL code to restrict the query to Student roles only

Average of ratings: -