Calculating average grades using varying scales in MySQL

I want to calculate the average grade of a bunch of rows in a MySQL table. The thing is, there may be a different scale used depending on what user you are logged in as.

Example scales can be:

• 1-5
• 1-10
• A-F (excluding E, obviously)

I开发者_运维百科n the numerical scales, the higher number is better, but in the alphabetic scale lower is better.

How would I make an SQL query to give the correct average?

Example averages:

• 5, 5, 4, 2 = 4
• 8, 10, 7, 6, 4 = 7
• A, A, C = B
• A, F, B, B = C
• F, F, B, D = D

The query I am using is:

SELECT CHAR(ROUND(AVG(ORD(UPPER(grade))))) AS average

The problem here is the last example. This comes out to be E using that query, but that's not a valid grade, it should be D.

I don't know how to handle this. What's the best way to get an average grade in MySQL if the scale has excluded values (such as the A-F scale excludes E)? I could calculate it in PHP if need also.

---

Without making a SQL query that looks like spaghetti, the best way to do this would be to create a mapping table with the grade->grade point values.

Such as

Table Values
grade  value
    A | 4
    B | 3
    C | 2
    etc.

Then, you'd do the average

SELECT ROUND(AVG(v.value)) FROM Grades g 
           INNER JOIN Values v on g.grade = v.grade

And from that, you could join back to the table to get the result

SELECT * FROM (
     SELECT ROUND(AVG(grade)) avg FROM Grades g 
               INNER JOIN Values v on g.value = v.value
             ) avgs 
INNER JOIN Values v2 on avgs.avg = v2.values

---

Create another table with the mapping between letter and value, join against it once to get the values for the current letters, and join against it a second time to convert the average value back into a letter.