CODEWITHSUNDEEP
phpmysqlsqlaverage
Alonso González Nestal
Alonso González Nestal

Reputation: 35

(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:

enter image description here

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)

Upvotes: 0

Views: 118

Answers (2)

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79875

I see three problems.

  • You should have GROUP BY id_pupil instead of just surname, in case two pupils have the same surname.
  • You have not specified any join condition between the two tables. This means that every pupil will get matched with every qualification - you really don't want that.
  • You don't need DISTINCT - in fact, it's usually a bad idea to use DISTINCT and GROUP BY in the same SQL statement.

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324840

You need to specify how the qualifications table should be joined to the pupils table.

select ... from pupils join qualifications using (id_pupil) where ...

Upvotes: 5

Related Questions