(MYSQL) Show list of qualifications of each pupil in columns

Question

I have this database and these data: http://sqlfiddle.com/#!2/b4cf72/1

My idea is to get this result:

enter image description here

But i don´t know how i can show the qualifications in columns in a same query.

On the other hand, i have another problem. There are 2 pupils (141 and 143) who show they haven´t any exam qualifications. I don´t know why is happening. They are in red on picture.

Consider to [edit](http://stackoverflow.com/posts/21294023/edit) your question and provide the desired output with qualifications in textual form (not an image). — peterm, Jan 22 '14 at 21:29
But it the same that the picture. I don´t understand you what you mean. — Alonso González Nestal, Jan 22 '14 at 21:30
In your question you say ***I don´t know** how i can show the **qualifications in columns** in a same query*. Is it something different from what you showed? — peterm, Jan 22 '14 at 21:37
Yes, my idea is to have 3 columns with the qualifications of each pupil in each field. For example, the pupil 1 has the qualifications: 3, 7.2 and 4 and i would like to show 3 | 7.2 | 4 — Alonso González Nestal, Jan 22 '14 at 21:40
OK. Is number of exam qualifications always constant and equals to 3? — peterm, Jan 22 '14 at 21:43
@peterm No, the maximum is 3 but it´s possible that it has only 2 or 1. — Alonso González Nestal, Jan 22 '14 at 21:45
@AlonsoGonzálezNestal Currently you have no proper means to tell apart first exam from second or third across all `pupil_id`s. `id_qualification` or `date` don't cut it. id's should't necessarily be in proper order so do dates. It's possible to first insert a row for the last exam and then for the first. Between these inserts results of the query will be incorrect. Technically rows can be enumerated using date as for ordering rows, but again it's unreliable and slow. One way to resolve this is to have a designated column that tells exactly what exam is this. — peterm, Jan 23 '14 at 00:11

score 2 · Accepted Answer · answered Jan 23 '14 at 01:07

i don´t know how i can show the qualifications in columns in a same query.

Currently you have no proper means to tell apart first exam from second or third across all pupil_ids. Using id_qualification or date doesn't cut it. id's should't necessarily be in proper order so do dates. It's possible to first insert a row for the last exam and then for the first. Between these inserts results of the query will be incorrect. Technically rows can be enumerated using date for ordering, but again it's unreliable and slow. One way to resolve this is to have a designated column that tells exactly what exam is this.

Having such column you can use conditional aggregation to easily produce necessary columns.

On the other hand, i have another problem. There are 2 pupils (141 and 143) who show they haven´t any exam qualifications.

You can correctly calculate your totals per each id_pupil this way

SELECT p.id_pupil, name, surname, 
       IFNULL(media, 0.00) media, 
       IFNULL(total_class, 0) total_class, 
       IFNULL(total_exams, 0) total_exams,
       IFNULL(total_miss, 0) total_miss,
       IFNULL(total_delay, 0) total_delay,
       IFNULL(total_attitude, 0) total_attitude,
       IFNULL(total_miss_justif, 0) total_miss_justif
  FROM pupils p LEFT JOIN
(
  SELECT p.id_pupil, 
         ROUND(AVG(CASE WHEN type_qualification='class' 
                        THEN qualification END), 2) media,
         SUM(type_qualification='class') total_class,
         SUM(type_qualification='exam') total_exams
    FROM pupils p JOIN qualifications q
      ON p.id_pupil = q.id_pupil
   WHERE p.level = 1
     AND p.class = 'A' 
     AND q.id_trimester = 1
   GROUP BY id_pupil
) q 
    ON p.id_pupil = q.id_pupil LEFT JOIN 
(
  SELECT p.id_pupil,
         SUM(type_incident = 'miss') total_miss,
         SUM(type_incident = 'delay') total_delay,
         SUM(type_incident = 'attitude') total_attitude,
         SUM(type_incident = 'miss_justif') total_miss_justif
    FROM pupils p JOIN incidents i
      ON p.id_pupil = i.id_pupil
   WHERE p.level = 1
     AND p.class = 'A' 
     AND i.id_trimester = 1
   GROUP BY p.id_pupil
) i
    ON p.id_pupil = i.id_pupil 
 WHERE p.level = 1
   AND p.class = 'A';

Output:

| ID_PUPIL |         NAME |          SURNAME | MEDIA | TOTAL_CLASS | TOTAL_EXAMS | TOTAL_MISS | TOTAL_DELAY | TOTAL_ATTITUDE | TOTAL_MISS_JUSTIF |
|----------|--------------|------------------|-------|-------------|-------------|------------|-------------|----------------|-------------------|
|      140 |        María |     Adámez Nieto |     4 |           2 |           3 |          2 |           1 |              1 |                 2 |
|      141 |       Daniel | Alonso Fernández |     6 |           3 |           2 |          0 |           0 |              0 |                 0 |
|      142 |        Rocío |   Anos Población |     4 |           1 |           3 |          1 |           1 |              0 |                 1 |
|      143 |       Teresa |  Arribas Miranda |   3.5 |           2 |           3 |          0 |           0 |              0 |                 0 |
|      144 | Isabel María |    Barroso Suero |   5.5 |           2 |           2 |          1 |           0 |              0 |                 1 |

Here is a SQLFiddle demo

(MYSQL) Show list of qualifications of each pupil in columns

1 Answers1