SQL query for Moodle to show space usage in Course Backup Area by course

MoodleCourseBackupArea.sql

SELECT mc.fullname AS CourseName, 
	ROUND((SUM(mf.filesize)/1024)/1024, 2) AS Storage_Used_MB
	
FROM mdl_files AS mf
LEFT JOIN mdl_context AS ctx ON mf.contextid = ctx.id
LEFT JOIN mdl_course AS mc ON ctx.instanceid = mc.id

WHERE mf.component = 'backup' 
AND mf.filearea = 'course'
AND NOT mf.filename = '.'

GROUP BY mf.contextid
ORDER BY Storage_Used_MB DESC

What is the purpose of "GROUP BY mf.contextid"?

@luisdev

Very good question, I haven't worked with moodle in several years so I don't remember much about the table structure. I seem to remember hating context tables because they were an intermediary table that was required for many to many relationships? I think files were deduplicated by file hash, so a file could exist in many courses, and a course can have many files? I think grouping by contextid was the quickest way to show files per course?

I could be totally remembering this wrong, so use my rambling at your own risk.

You're better off looking at the ad-hoc reports page in the documentation, I think I stole most of what we used from there. https://docs.moodle.org/311/en/ad-hoc_contributed_reports

SELECT
c.id AS course_id,
c.fullname AS course_name,
SUM(f.filesize) AS total_backup_space
FROM
mdl_course c
JOIN
mdl_files f ON c.id = f.contextid
JOIN
mdl_backup_courses bc ON c.id = bc.courseid
WHERE
f.component = 'backup'
GROUP BY
c.id, c.fullname
ORDER BY
total_backup_space DESC;

calculates the total backup space used by each course by summing up the file sizes from the mdl_files table where the component is 'backup.' It provides a list of courses along with their corresponding total backup space, ordered in descending order.

Additionally, always back up your database before running any queries.