SUBSCRIBE

Useful SQL scripts

AI in Education Leaderboard Post Page
Ai In Education Square Post Page

--- Advertisement ---

If you are a Moodle site administrator, then probably this is the best collection of SQL queries which can be utilized to make custom reports on your Moodle site.

Few of the useful SQl queries are:

Student (user) COUNT in each Course

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
# AND course.fullname LIKE '%2013%'
GROUP BY course.id
ORDER BY COUNT(course.id) DESC

Most popular ACTIVITY

SELECT COUNT(l.id) hits, module
FROM prefix_log l
WHERE module != 'login' AND module != 'course' AND module != 'role'
GROUP BY module
ORDER BY hits DESC

Courses without Teachers

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Teachers
FROM prefix_course AS c
ORDER BY Teachers ASC


You can also use the popular report plugins like:
Ad-hoc database queries : https://moodle.org/plugins/view/report_customsql
Configurable Reports : https://moodle.org/plugins/view/block_configurable_reports

6 Responses

  1. can u please share the query of
    how many empty courses are in moodle. Threre are no any content in the course.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

The Latest

The eLearn Podcast

--- Advertisement ---

Subscribe to our newsletter

Education technology has the power to change lives. 

To get the latest news, information and resources about online learning from around the world by clicking on the button below.