How to sort test score average of a class?

Hi,

The fields in the table class are student_name, test_score1, test_score2, test_score3.

How do I have a result of listing of student_name, test_score1, test_score2, test_score3 and average_score in order that the highest average student shows on top?

(There is no existing field for average in the table. If the student only took 2 test, the average will be total scores/ 2, not divided by 3.)

I am guessing it will look like the following except I don't know how to get the count of test taken.

select student_name, test_score1, test_score2, test_score3 from class order by (test_score1 + test_score2 + test_score3) / "count of test taken" DESC


Thanks for any help in advance.

 

you need this directly into sql or php?

 

Hi,
I've tested this query and it works perfect

SELECT student_name,
test_score1,
test_score2,
test_score3,
((IF(test_score1 IS NULL,0,test_score1)+IF(test_score2 IS NULL,0,test_score2)+IF(test_score3 IS NULL,0,test_score3))/IF((IF(test_score1 IS NULL,0,1)+IF(test_score2 IS NULL,0,1)+IF(test_score3 IS NULL,0,1))=0,1,IF(test_score1 IS NULL,0,1)+IF(test_score2 IS NULL,0,1)+IF(test_score3 IS NULL,0,1))) average
FROM class ORDER BY average DESC;

Code (markup):

Regards,
Nick

 

Nick,

Thank you very much. I was confused with so many if in your code at first. I made it a bit simpler with your help and it works. Thanks again for the kind help.

 

If you plan on having any more quizzes I suggest you normalize your data. Look how long that conditional statement is in the query. And that's with just 3 quizzes. Its going to get burn-victim ugly really fast if you try and add any more quizzes to that table as fields.

If you make a seperate table for quiz scores you can easily use the AVG function of mysql to do all that work for you.

 

I was going to suggest the same thing. Storing data like this is called denormalization. It goes against the grain of how a database is designed to operate. There are a few cases where denormalization works, but this is not one of them.

Ideally you want to have a student table, and then separately a test table, with a foreign key relation between the tests and students. You can then perform any sort of mathematical function based on specific tests, scores, dates, test numbers, or whatever else you need.

 

@aayybb glad to help Hope that decreasing if statements will not cause division by zero in some cases (I didn't know can score fields be NULL or they have default value, so I've used so many IFs)!

Sometimes denormalization is required to keep right the database normal form.

Looking at table structure, I guess that student_name is primary/unique key, so there is no problem to use AVG function in virtual tables:

SELECT t1.*,SCORE FROM class t1 NATURAL JOIN (
SELECT student_name,AVG(score) SCORE FROM
((SELECT student_name,test_score1 score FROM class)
UNION ALL
(SELECT student_name,test_score2 FROM class)
UNION ALL
(SELECT student_name,test_score3 FROM class)
) A
GROUP BY 1
ORDER BY 2 DESC) B
ORDER BY SCORE DESC;

Code (markup):

Best regards,
Nick

 

Any good mysql book (not too dry) to read?

 

Click me
Regards,
Nick