sql case statement of case results-CodePudding

I want to take letter grades convert to a number average and then convert back to a letter. The

select 
  avg(case when grade = 'A' then 4
           when grade = 'B' then 3
           when grade = 'C' then 2
           when grade = 'F' then 1 end)
from student
where id = 'test'

this works perfectly I want to now convert the average back to a letter.

I tried and it works but I cant help but think there isn't a better easier way to accomplish this

select case(when (select 
  avg(case when grade = 'A' then 4
           when grade = 'B' then 3
           when grade = 'C' then 2
           when grade = 'F' then 1)
from student where id = 'test') = 4 then 'A'

CodePudding user response：

Use another CASE expression on the first one:

SELECT
    CASE WHEN num_avg <= 1.0 THEN 'F'
         WHEN num_avg <= 2.0 THEN 'C'
         WHEN num_avg <= 3.0 THEN 'B'
         ELSE 'A' END AS avg_letter_grade
FROM
(
    SELECT AVG(CASE grade WHEN 'A' THEN 4.0
                          WHEN 'B' THEN 3.0
                          WHEN 'C' THEN 2.0
                          WHEN 'F' THEN 1.0 END) AS num_avg
    FROM student
    WHERE id = 'test'
) t;

The letter grade assignments I used in the outer query are not in agreement with what my understanding of typical grade scales are, but it is in line with your scale, so I chose to use it.