This first one counts up the modules added to a given course, just change the course id number for "c_modules.course" in the WHERE clause...
SELECT modules.name, COUNT(c_modules.id) AS "Module Count"
FROM
mdl_course_modules c_modules
INNER JOIN mdl_modules modules ON c_modules.module = modules.id
WHERE
c_modules.course = 560
AND c_modules.visible = 1
GROUP BY
modules.name
FROM
mdl_course_modules c_modules
INNER JOIN mdl_modules modules ON c_modules.module = modules.id
WHERE
c_modules.course = 560
AND c_modules.visible = 1
GROUP BY
modules.name
This next one produces a basic report with a row per course, counting the amount of students, student course views (hits) and the average view per student, a few other peices of data (such as the path and a few id's) are thrown in to make it useful within a web application. Also, you may wish to ignore the "TopLevelCatName", I store our cost centre codes in the category description field...
SELECT
y.TopLevelCatID,
description AS TopLevelCatName,
y.CategoryID,
y.CategoryName,
y.CategoryPath,
y.CourseID,
y.CourseName,
y.Views,
y.Students,
y.ViewsPerStudent
FROM
mdl_course_categories cats
INNER JOIN (
SELECT
x.CourseID,
x.CourseName,
x.Views,
count(*) AS Students,
(x.Views/count(*)) AS ViewsPerStudent,
TRIM(LEADING '/' FROM SUBSTRING_INDEX(x.CategoryPath, '/', 2)) AS TopLevelCatID,
x.CategoryID,
x.CategoryName,
x.CategoryPath
FROM
mdl_role_assignments roles
INNER JOIN (
SELECT
course.id AS CourseID,
course.fullname AS CourseName,
count(*) AS Views,
categories.id AS CategoryID,
categories.name AS CategoryName,
categories.path AS CategoryPath,
context.id AS ContextID
FROM
mdl_course_categories categories
INNER JOIN mdl_course course ON categories.id = course.category
INNER JOIN mdl_log log ON course.id = log.course
INNER JOIN mdl_context context ON course.id = context.instanceid
WHERE
log.module = "course"
AND log.action = "view"
AND context.contextlevel = '50'
GROUP BY
categories.id,
categories.name,
categories.path,
course.fullname,
course.id
) x ON roles.contextid = x.ContextID
WHERE
roles.roleid = '5'
GROUP BY
x.CourseID,
x.CourseName,
x.CategoryID,
x.CategoryName,
x.CategoryPath,
x.Views
) y ON cats.id = y.TopLevelCatID
ORDER BY
y.CategoryName, y.CourseName
y.TopLevelCatID,
description AS TopLevelCatName,
y.CategoryID,
y.CategoryName,
y.CategoryPath,
y.CourseID,
y.CourseName,
y.Views,
y.Students,
y.ViewsPerStudent
FROM
mdl_course_categories cats
INNER JOIN (
SELECT
x.CourseID,
x.CourseName,
x.Views,
count(*) AS Students,
(x.Views/count(*)) AS ViewsPerStudent,
TRIM(LEADING '/' FROM SUBSTRING_INDEX(x.CategoryPath, '/', 2)) AS TopLevelCatID,
x.CategoryID,
x.CategoryName,
x.CategoryPath
FROM
mdl_role_assignments roles
INNER JOIN (
SELECT
course.id AS CourseID,
course.fullname AS CourseName,
count(*) AS Views,
categories.id AS CategoryID,
categories.name AS CategoryName,
categories.path AS CategoryPath,
context.id AS ContextID
FROM
mdl_course_categories categories
INNER JOIN mdl_course course ON categories.id = course.category
INNER JOIN mdl_log log ON course.id = log.course
INNER JOIN mdl_context context ON course.id = context.instanceid
WHERE
log.module = "course"
AND log.action = "view"
AND context.contextlevel = '50'
GROUP BY
categories.id,
categories.name,
categories.path,
course.fullname,
course.id
) x ON roles.contextid = x.ContextID
WHERE
roles.roleid = '5'
GROUP BY
x.CourseID,
x.CourseName,
x.CategoryID,
x.CategoryName,
x.CategoryPath,
x.Views
) y ON cats.id = y.TopLevelCatID
ORDER BY
y.CategoryName, y.CourseName
