Here is a query to display the list of courses with its category and number of enrolments, number of inprogress and number of completions for each course.
SELECT min(base.id) AS id, course.fullname AS course_courselink,
course.id AS course_id, category.name as category_name,
COUNT(base.status) AS course_completion_status,
SUM(CASE WHEN base.status = 25 THEN 1 ELSE 0 END) AS course_completion_isinprogress,
SUM(CASE WHEN base.status = 50 OR base.status = 75 THEN 1 ELSE 0 END) AS course_completion_iscomplete
FROM mdl_course_completions base
LEFT JOIN mdl_course course ON course.id = base.course
LEFT JOIN mdl_course_categories category ON course.category = category.id
WHERE ( 1=1 )
GROUP BY course.fullname, course.id, course.visible, course.audiencevisible
order by category_name
