How to change a letter grade into numeric in MySQL using the "case when" statement-CodePudding

I am not able to change a letter grade into numeric in MySQL using the "case when" statement. The column in question “GRADE” is defined as varchar(50) in MySQL.

Select, ID,
CASE 
WHEN GRADE < 60 then 0.0
WHEN GRADE= 60 then 1.0
WHEN GRADE <= 62 then 1.3
WHEN GRADE <= 65 then 1.7
WHEN GRADE <= 68 then 2.0
WHEN GRADE <= 71 then 2.3
WHEN GRADE <= 74 then 2.7
WHEN GRADE <= 78 then 3.0
WHEN GRADE <= 82 then 3.3
WHEN GRADE <= 86 then 3.7
WHEN GRADE <= 92 then 4.0
WHEN GRADE <= 100 then 4.3 
--
WHEN GRADE = 'F' then 0.0
WHEN GRADE = 'D' then 1.0
WHEN GRADE = 'D ' then 1.3
WHEN GRADE = 'C-' then 1.7
WHEN GRADE = 'C' then 2.0
WHEN GRADE = 'C ' then 2.3
WHEN GRADE = 'B-' then 2.7
WHEN GRADE = 'B' then 3.0
WHEN GRADE = 'B ' then 3.3
WHEN GRADE = 'A-' then 3.7
WHEN GRADE = 'A' then 4.0
WHEN GRADE = 'A ' then 4.3
else null
end as Quality_Point,
from table_query_2022

I keep on getting the below with an output of "0.0" while converting letter grades:

ID  Grade   Quality_Point
1   50         0.0
2   66          2
3   80         3.3
4   67          2
5   69         2.3
6   A          0.0
7   D          0.0
8   B-         0.0
9   B          0.0
10  A-         0.0
11  F          0.0

Instead of getting the below

ID  Grade   Quality_Point
1   50          0.0
2   66           2
3   80          3.3
4   67           2
5   69          2.3
6   A           4.0
7   D           1.0
8   B-          2.7
9   B           3.3
10  A-          3.7
11  F           0.0

CodePudding user response：

It should work if you flip the order of the letter grades and the number grades in your case expression:

Select ID, GRADE, 
CASE 
WHEN GRADE = 'F' then 0.0
WHEN GRADE = 'D' then 1.0
WHEN GRADE = 'D ' then 1.3
WHEN GRADE = 'C-' then 1.7
WHEN GRADE = 'C' then 2.0
WHEN GRADE = 'C ' then 2.3
WHEN GRADE = 'B-' then 2.7
WHEN GRADE = 'B' then 3.0
WHEN GRADE = 'B ' then 3.3
WHEN GRADE = 'A-' then 3.7
WHEN GRADE = 'A' then 4.0
WHEN GRADE = 'A ' then 4.3
--
WHEN GRADE < 60 then 0.0
WHEN GRADE= 60 then 1.0
WHEN GRADE <= 62 then 1.3
WHEN GRADE <= 65 then 1.7
WHEN GRADE <= 68 then 2.0
WHEN GRADE <= 71 then 2.3
WHEN GRADE <= 74 then 2.7
WHEN GRADE <= 78 then 3.0
WHEN GRADE <= 82 then 3.3
WHEN GRADE <= 86 then 3.7
WHEN GRADE <= 92 then 4.0
WHEN GRADE <= 100 then 4.3 
else null
end as Quality_Point
from table_query_2022

Fiddle