SELECT
                          program_level.id,
                          program_level.name,
                          -- group_concat(concat(units.course_unit_code, '-', passed_papers.result)),
                          count(curriculum_unit.units_id)                                             AS tt_units,
                          COUNT(IF(passed_papers.id IS NOT NULL OR exempted.id IS NOT NULL, 1, NULL)) AS units_completed
                        FROM enrollment
                          INNER JOIN curriculum ON enrollment.curriculum_id = curriculum.id
                          INNER JOIN curriculum_unit ON curriculum.id = curriculum_unit.curriculum_id
                          INNER JOIN program ON curriculum.program_id = program.id
                          INNER JOIN program_level ON curriculum_unit.program_level_id = program_level.id
                          INNER JOIN units ON curriculum_unit.units_id = units.id
                          LEFT JOIN (SELECT DISTINCT
                                       -- passed exams: am using a derived table so that i
                                       -- do not get duplicates coming from multiple attempted results
                                       u.id,
                                       u.course_unit_code,
                                       r.result
                                     FROM results r
                                       INNER JOIN enrollment e ON r.student_id = e.id
                                       INNER JOIN units u ON r.exam_id = u.id
                                     WHERE r.result >= 50 AND e.reg_no = 'TE/000975/19') AS passed_papers ON units.id = passed_papers.id
                          LEFT JOIN (SELECT DISTINCT
                                       -- exempted units
                                       u.id,
                                       u.course_unit_code,
                                       eu.approval
                                     FROM exam_exemption ee
                                       INNER JOIN exemption_unit eu ON ee.id = eu.exam_exemption_id
                                       INNER JOIN units u ON eu.exam_id = u.id
                                       INNER JOIN enrollment e ON ee.student_id = e.id
                                     WHERE eu.approval = 'approved' AND e.reg_no = 'TE/000975/19') AS exempted ON exempted.id = units.id
                        WHERE reg_no = 'TE/000975/19'
                        GROUP BY program_level.id
                        ORDER BY program_level.precedence