Finding averages from changing totals.

[drisconsult]

Just to reiterate my problem. It all began for me this year when GCSE marking was changed to grades 9 to 1 and U. Grade 9 being the highest . The only problem I have is, how do I find the average of the total grades of a student if the total subjects taken can differ with every student. They are 12 compulsory subjects in the UK GCSE, plus two options. In one class, one student may have taken 12 subjects, the next student may have taken 12 plus one option. How can I find the average grade when each student can have taken a different number of subjects. When it was static, there were no problems, I simply divided the total be the number of subjects taken. But now!
Terence Driscoll, London.

 

[drisconsult]

Please excuse the missing question marks. I am both tired and frustrated. Terence Driscoll

 

[plog]

how do I find the average of the total grades of a student if the total subjects taken can differ with every student.

With a properly normalized database its a simple query:

SELECT StudentID, AVG(Grade) AS AverageGrade FROM StudentGrades GROUP BY StudentID

Is your database properly normalized? Show us the table structure if the above query doesn't help you.

 

[MajP]

It is a simple weighted average.
Student 1 takes 12 Classes with a grade point average of 3.0
Student 2 takes 10 classes with a gpa of 2.75
Student 3 is brand new and has completed 4 classes with a 4.0 grade.

Student 1's average should be worth more student 3 worth proportionally less. The overall average is

12/(12+10 + 4) * 3.0 + 10/26 * 2.75 + 4/26 * 4 = 3.01