CODEWITHSUNDEEP
phpmysqlaverage
gen_Eric
gen_Eric

Reputation: 227310

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:

In 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:

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.

Upvotes: 0

Views: 1969

Answers (2)

dfb
dfb

Reputation: 13289

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

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799580

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.

Upvotes: 1

Related Questions