Mysql – calculate average of a class where students offer different number of subjects

group byMySQL

I am trying to calculate the class average of a class where students can hold different amounts of subjects (e.g.: two students in a class of which one takes 3 subjects and the other 2). How do I get the class average that applies to all students? I already have their total term score in a table. I know the formula for calculating the average is simply the total averages of individual students divided by the number of students, but I can't seem to get it working. Can anyone please help me write a query that will calculate and print out this class average?

Here is what I have tried so far:

SELECT student_id, SUM(CA_total)/count(term_total) AS average
FROM score_entry
GROUP BY student_id

result from mysql db

BUT I want the final result to be the sum of 25 + 26.5 which will be 51.5 then divided by the 2 students in the class should give 25.75. this is the class average. I want the query or code that will echo out the final answer 25.75. thanks
THIS IS SAMPLE OF THE EXACT DATABASE
enter image description here

Best Answer

SUM(..)/count(..) is close. Instead:

SUM(sums) / SUM(counts)

That is, each subtotal (student?) has a sum of the scores and a count of the number of items (subjects)

Related Question