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

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

`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)

• If you need further assistance, provide an example of the raw data. Commented Jul 22, 2016 at 18:41

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