This report groups all Moodle courses for a given term by department with activity status.

allcoursesbydepartment.sql

SELECT department,
       COUNT(*) AS courses,
       SUM(CASE
               WHEN active=1 THEN 1
               ELSE 0
           END) active,
       SUM(CASE
               WHEN active=0 THEN 1
               ELSE 0
           END) inactive
FROM
  (SELECT LEFT(c.fullname, LOCATE(' ', c.fullname) - 1) AS department,
          CASE
              WHEN EXISTS
                     (SELECT id
                      FROM prefix_logstore_standard_log lsl
                      WHERE lsl.courseid=c.id
                        AND (lsl.crud='c'
                             OR lsl.crud='u')
                        AND lsl.userid <> 0
                        AND lsl.userid <> 2
                      LIMIT 1)
                   AND NOT EXISTS
                     (SELECT id
                      FROM prefix_enrol e
                      WHERE e.enrol='meta'
                        AND (e.courseid=c.id
                             OR e.customint1=c.id)
                      LIMIT 1) THEN 1
              WHEN EXISTS
                     (SELECT id
                      FROM prefix_enrol e
                      WHERE e.enrol='meta'
                        AND e.customint1=c.id
                      LIMIT 1) THEN 1
              ELSE 0
          END AS active
   FROM prefix_course c
   WHERE EXISTS
       (SELECT id
        FROM prefix_course_categories cc
        WHERE cc.id=c.category
          AND cc.parent=:term)
     AND NOT EXISTS
       (SELECT id
        FROM prefix_enrol e
        WHERE e.enrol='meta'
          AND e.courseid=c.id)
     AND c.shortname NOT LIKE 'EXCL%'
     AND c.fullname NOT LIKE '%Independent%'
     AND c.fullname NOT LIKE '%Thesis%') t1
GROUP BY department