Mysql: Comma-separated List Of Numbers Of Items

I have a learning system based on students watching videos then answering questions attached to those videos. The (simplified) model is basically this: Courses (a course definition

Solution 1:

You need to aggregate twice. One method is before doing the join:

SELECT ci.id,
       COUNT(v.id) AS num_vids,
       GROUP_CONCAT(q.numquestions ORDERBY q.video_id) AS questions
FROM course_instances ci LEFT JOIN
     videos v
     ON v.course_id = ci.course_id LEFT JOIN
     (SELECT q.video_id, COUNT(*) as numquestions
      FROM questions q
      GROUPBY q.video_id
     ) q
     ON q.video_id = v.id
GROUPBY ci.id;

The subquery counts the number of questions for each video. The outer query then concatenates these together into the result string that you want.