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

2 Answers 2


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.
    – Rick James
    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)
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.