CODEWITHSUNDEEP
mysqlsql
Mitya
Mitya

Reputation: 34556

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, e.g. Basic Algebra)

----------------------------------------
| id   | int     | auto_increment | PK |
| name | varchar |                |    |
----------------------------------------

Course instances (an entry is logged in here when a user begins a course)

---------------------------------------------
| id        | int     | auto_increment | PK |
| course_id | int     |                | FK |
---------------------------------------------

Videos (associated with a given course)

---------------------------------------------
| id        | int     | auto_increment | PK |
| course_id | int     |                | FK |
| name      | varchar |                |    |
---------------------------------------------

Questions (associated with a given video)

--------------------------------------------
| id       | int     | auto_increment | PK |
| video_id | int     |                | FK |
| q_text   | varchar |                |    |
--------------------------------------------

What I want is to get all course instances and the number of questions per video, comma separated.

So for example, if a course had three videos attached to it, and the first video had two questions and the second and third video had one question each, I want this:

-----------------------------------
| course_instance_id | qs_per_vid |
-----------------------------------
| 1                  | 3,2,2      |
-----------------------------------

The best I've got from my own efforts is this:

SELECT
    ci.id,
    COUNT(DISTINCT videos.id) AS num_vids,
    GROUP_CONCAT(questions.video_id ORDER BY questions.video_id) AS questions
FROM course_instances ci
LEFT JOIN videos ON videos.course_id = ci.course_id
LEFT JOIN questions ON questions.video_id = videos.id
GROUP BY ci.id

That produces something like:

---------------------------------
| id | num_vids | questions     | <-- actually it's the vid ID of each question
---------------------------------
| 1  | 4        | 1,1,1,2,2,3,3 |
---------------------------------

...from which I can infer, by the repeated video ID sequences, that the first vid has three questions, and the second and third two.

But how can I end up with my desired output above?

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

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 ORDER BY 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
      GROUP BY q.video_id
     ) q
     ON q.video_id = v.id
GROUP BY 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.

Upvotes: 2

Related Questions