Mysql: assign an integer value to String values ​​and get the average value

Question

I am using mysql database. I have a table called feedback, which has a howIsTraining field which can have one value out of five values: “Excellent, very good, good, bad and sorry”

I want to write a query that will give the average value of this value from all the data in the following format

I want to give an integer value for each of the values

Excellent = 5 Very Good = 4 Good = 3 Poor = 2 Pathetic = 1

if I say that I have five entries in the table Excellent, very good, excellent, good, bad it means ((5 + 4 + 5 + 3 + 2) / 5) = 3.8

finally he will give the result as 3.8

Answer 1

select avg(case howIsTraining when 'Excellent' then 5 when 'Very Good' then 4 when 'Good' then 3 when 'Poor' then 2 when 'Pathetic' then 1 else null end) as avg_rating from feedback 

SQLFiddle: http://sqlfiddle.com/#!2/14a06/1

But you should really think of a better design (like as shown by Jack)

Answer 2

Assuming you have another table that stores grades:

 scores ----------+------ term | value ----------+------ Excellent | 5 Very Good | 4 ... 

Then you will have this request:

 SELECT AVG(scores.value) FROM feedback INNER JOIN scores ON term = howIsTraining; 

Answer 3

Wouldn't it make sense to have an integer column in the database with values ​​from 1 to 5. And then when you present the data, you convert the number to a string?

In extreme cases, add an additional integer column and fill it with the correct values ​​of the UPDATE class ... SET grade = 1 where hoIsTraing = 'Pathetic'.