How to calculate average of list of qualifications of each pupil MYSQL I have this database and query:

http://sqlfiddle.com/#!2/b4cf72/116

And I would like to add a column with the average of the exam qualifications. However, I try doing this but it tells me there are more than 1 row on query:

http://sqlfiddle.com/#!2/b4cf72/115

    SELECT pupils.id_pupil, NAME, surname
       ,IFNULL(qua.media, 0)     media
       ,IFNULL(qua.count, 0)     number_class_qualifications
       ,(
         SELECT AVG(qualification)
           FROM   qualifications
                  JOIN pupils USING (id_pupil)
           WHERE  type_qualification     = 'exam'
                  AND id_trimester       = 1
                  AND LEVEL              = 1
                  AND class              = 'A'
           GROUP BY id_pupil
       ) AS exam_average,
       SUM(CASE WHEN type_incident = 'miss' AND incidents.id_trimester = 1 THEN 1 ELSE 0
           END) AS misses,
       SUM(CASE WHEN type_incident = 'delay' AND incidents.id_trimester = 1 THEN 1 ELSE 0
           END) AS delays,
       SUM(CASE WHEN type_incident='attitude' AND incidents.id_trimester = 1 THEN 1 ELSE 0
           END) AS attitude,
       SUM(CASE WHEN type_incident='miss_justif' AND incidents.id_trimester=1 THEN 1 ELSE 0
           END) AS misses_justificadas
FROM   pupils
       LEFT JOIN incidents
            ON  incidents.id_pupil = pupils.id_pupil
       LEFT JOIN (
                SELECT id_pupil,
                       id_trimester,
                       COUNT(qualification) COUNT,
                       ROUND(AVG(qualifications.qualification), 2) media
                FROM   qualifications
                WHERE  type_qualification = 'class'
                GROUP BY
                       id_pupil,
                       id_trimester
            )                AS qua
            ON  (qua.id_pupil = pupils.id_pupil)
WHERE  LEVEL = 1
       AND class = 'A'
       AND qua.id_trimester = 1
GROUP BY pupils.id_pupil


The problem is on this sentence:

SELECT AVG(qualification)
FROM   qualifications
   JOIN pupils USING (id_pupil)
WHERE  type_qualification = 'exam'
       AND id_trimester   = 1
       AND LEVEL          = 1
       AND class          = 'A'
    GROUP BY id_pupil


How can I solve it?
Your current subquery is returning the avg(qualification) for all pupils but it is not relating each row back to a single pupil.  You need to make this a correlated subquery which will query your qualifications while using the pupils from your outer query.  I would suggest rewriting the subquery query the following way:

(select avg(q.qualification) 
from qualifications q
where type_qualification='exam'
  and p.id_pupil = q.id_pupil
  and q.id_trimester=1 
group by q.id_pupil) as exam_average


You will notice that this subquery doesn't have the JOIN written inside of it but it is referencing your pupils table from the main ares of your query.  The full query would be written:

SELECT 
  p.id_pupil,
  p.name,
  p.surname,
  IFNULL(qua.media, 0) media,
  IFNULL(qua.count, 0) number_class_qualifications,
  (select avg(q.qualification) 
   from qualifications q
   where type_qualification='exam'
     and p.id_pupil = q.id_pupil
     and q.id_trimester=1 
   group by q.id_pupil) as exam_average,
  sum(case when i.type_incident='miss' and i.id_trimester=1 then 1 else 0 end) as misses,
  sum(case when i.type_incident='delay' and i.id_trimester=1 then 1 else 0 end) as delays,
  sum(case when i.type_incident='attitude' and i.id_trimester=1 then 1 else 0 end) as attitude,
  sum(case when i.type_incident='miss_justif' and i.id_trimester=1 then 1 else 0 end) as misses_justificadas 
FROM pupils p
LEFT JOIN incidents i
  ON i.id_pupil=p.id_pupil
LEFT JOIN
(
  select id_pupil, id_trimester, 
   count(qualification) count, 
     round(avg(qualifications.qualification),2) media
  from qualifications 
  where type_qualification='class'
  group by id_pupil, id_trimester 
) as qua
  ON qua.id_pupil = p.id_pupil
WHERE p.level=1
  and p.class='A' 
  and qua.id_trimester=1 
GROUP BY p.id_pupil, p.name, p.surname, qua.media, qua.count;


See SQL Fiddle with Demo

This could also be written without using the correlated subquery and you would JOIN on the subquery similar to:

SELECT 
  p.id_pupil,
  p.name,
  p.surname,
  IFNULL(qua.media, 0) media,
  IFNULL(qua.count, 0) number_class_qualifications,
  IFNULL(q.exam_average, 0) exam_average,
  sum(case when i.type_incident='miss' and i.id_trimester=1 then 1 else 0 end) as misses,
  sum(case when i.type_incident='delay' and i.id_trimester=1 then 1 else 0 end) as delays,
  sum(case when i.type_incident='attitude' and i.id_trimester=1 then 1 else 0 end) as attitude,
  sum(case when i.type_incident='miss_justif' and i.id_trimester=1 then 1 else 0 end) as misses_justificadas 
FROM pupils p
LEFT JOIN incidents i
  ON i.id_pupil=p.id_pupil
LEFT JOIN
(
  select id_pupil, id_trimester, 
   count(qualification) count, 
     round(avg(qualifications.qualification),2) media
  from qualifications 
  where type_qualification='class'
  group by id_pupil, id_trimester 
) as qua
  ON qua.id_pupil = p.id_pupil
LEFT JOIN
(
  select avg(q.qualification) as exam_average, q.id_pupil
  from qualifications q
  where q.type_qualification='exam'
    and q.id_trimester=1 
  group by q.id_pupil
) q
  on p.id_pupil = q.id_pupil
WHERE p.level=1
  and p.class='A' 
  and qua.id_trimester=1 
GROUP BY p.id_pupil, p.name, p.surname, qua.media, qua.count, q.exam_average;


See SQL Fiddle with Demo. Both will generate the result:

| ID_PUPIL |         NAME |          SURNAME | MEDIA | NUMBER_CLASS_QUALIFICATIONS |   EXAM_AVERAGE | MISSES | DELAYS | ATTITUDE | MISSES_JUSTIFICADAS |
|----------|--------------|------------------|-------|-----------------------------|----------------|--------|--------|----------|---------------------|
|      140 |        María |     Adámez Nieto |     4 |                           2 | 4.733333269755 |      2 |      1 |        1 |                   2 |
|      141 |       Daniel | Alonso Fernández |     6 |                           3 | 8.400000095367 |      0 |      0 |        0 |                   0 |
|      142 |        Rocío |   Anos Población |     4 |                           1 | 6.366666634878 |      1 |      1 |        0 |                   1 |
|      143 |       Teresa |  Arribas Miranda |   3.5 |                           2 | 6.766666650772 |      0 |      0 |        0 |                   0 |
|      144 | Isabel María |    Barroso Suero |   5.5 |                           2 |              4 |      1 |      0 |        0 |                   1 |