CODEWITHSUNDEEP
mysql
Zestyy99
Zestyy99

Reputation: 307

Getting an average grade from a database

I'm making a quiz system, and i'm working on displaying an average grade for the user. So for example If I have table like this: 

    +-----------+-------------+-------------------+
    |userid     | user_score  |number_of_questions|
    +-----------+-------------+-------------------+
    |  1        |      10     |        10         |   
    |  1        |      10     |        10         |
    |  1        |      10     |        10         |
    |  1        |      10     |        10         |
    |  1        |      9      |        10         |
    |  1        |      4      |        5          |
    |  1        |      4      |        5          |
    |  1        |      5      |        5          |
    |  1        |      5      |        5          |
    +-----------+-------------+-------------------+

Is there anyway of getting an average grade?

EDIT: I have changed the grades from letters to scores, as I have been made aware that you cannot get averages for non numeric values.

How would I go about doing a query to get the average of user score for both number of questions?

Upvotes: 2

Views: 359

Answers (2)

Tim Morton
Tim Morton

Reputation: 2644

You’re asking for the query, so the simple answer would be

Select avg(user_score) 
From {table_name}
Where {your criteria}
Group by user_id

However, your client will be rather disappointed with your calculations—getting 5 points out of 5 and 100 out of 100 is quite different than averaging 5 and 100!

So I would suggest using percentage instead of raw score:

Select avg(user_score/number_of_questions)

Upvotes: 1

Manuel Otto
Manuel Otto

Reputation: 6540

You could convert the grades to integers using ASCII(), then convert the average back to grades using CHAR().

SELECT CHAR(ROUND(AVG(ASCII(`grade`)-65))+65) FROM table

Please note this doesn't take the + into account. You'd first have to decide the numeric value of a + on a grade.

Upvotes: 0

Related Questions