Q:

(MYSQL) Calculate average from a list of pupils who have some qualifications

I would like to get the average of qualifications of each pupil. 
For example, there are 30 pupils who have:
Pupil 1: Qualification 1: 5,6 Qualification 2: 3,2 Qualification 3: 9,1
Pupil 2: Qualification 1: 5,1 Qualification 2: 8,6 Qualification 3: 3,1
Pupil 3: Qualification 1: 1,9 Qualification 2: 7,2 Qualification 3: 5,1
Pupil 4: Qualification 1: 4,6 Qualification 2: 5,2 Qualification 3: 9,5

etc...
And i want to get: 

I´ve tried with:
select distinct pupils.name, pupils.surname, qualifications.id_trimester, round(avg(qualifications.qualification),2), count(qualifications.qualification) from pupils, qualifications where pupils.level='1' and pupils.class='A'  and qualifications.id_trimester=1 and qualifications.type_qualification='class' group by pupils.surname.

But it shows the same average qualification and count for all the rows. It shows the average qualification of the first pupil repeated in all the rows...
The tables are:
pupils:

id_pupil
name
surname
email
user
pass
level
class

qualifications:

id_qualification
qualification
date
time
subject
id_pupil
id_user
id_trimester
type_qualification (misses, delayes or attitude)

A:

You need to specify how the qualifications table should be joined to the pupils table.
select ... from pupils join qualifications using (id_pupil) where ...

