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

Question

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

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

Answer 1

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)

Answer 2

Like Rick points out, you should provide CREATE TABLE and INSERT statements. That said, I guess something like:

SELECT AVG(average)
FROM (
    SELECT student_id, AVG(CA_total)  -- assuming CA_total and term_total are not null
    FROM score_entry
    GROUP BY student_id
) AS T

will do