Is there a way I can shorten this query?-CodePudding

I'm solving a task and I managed to find a solution but I'm not sure if this is the best way to write this query

SELECT 
    students.studentname,
    CASE 
        WHEN (AVG(courses_student.exam_season_one)    
              AVG(courses_student.exam_season_two)   
              AVG(courses_student.degree_season_one)   
              AVG(courses_student.degree_season_two)) / 4 >= 80 
            THEN 'EXCELLENT'
        WHEN (AVG(courses_student.exam_season_one)    
              AVG(courses_student.exam_season_two)   
              AVG(courses_student.degree_season_one)   
              AVG(courses_student.degree_season_two)) / 4 >= 70 
            THEN 'VERY GOOD'
        WHEN (AVG(courses_student.exam_season_one)    
              AVG(courses_student.exam_season_two)   
              AVG(courses_student.degree_season_one)   
              AVG(courses_student.degree_season_two)) / 4 >= 60 
            THEN 'GOOD'
        WHEN (AVG(courses_student.exam_season_one)    
              AVG(courses_student.exam_season_two)   
              AVG(courses_student.degree_season_one)   
              AVG(courses_student.degree_season_two)) / 4 >= 50 
            THEN 'ACCEPTABLE'
        ELSE 'FAIL'
    END AS GRADE
FROM
    courses_student
JOIN 
    students ON students.student_id = courses_student.student_id
GROUP BY 
    students.studentname

As you can see I repeated this one :

    WHEN (AVG(courses_student.exam_season_one)    
          AVG(courses_student.exam_season_two)   
          AVG(courses_student.degree_season_one)   
          AVG(courses_student.degree_season_two)) / 4

four times! And it looks messy, so is there a way I can make it shorter like writing it only once and just use a single word instead? (I tried to use "AS" it didn't work)

CodePudding user response：

For clarity, testing, and debugging purposes you can avoid repeating the same long expression by including it in subquery. For example:

select studentname,
  case when av >= 80 then 'EXCELLENT'
       when av >= 70 then 'VERY GOOD'
       when av >= 60 then 'GOOD'
       when av >= 50 then 'ACCEPTABLE'
       else 'FAIL'
  end as grade
from (
  SELECT s.studentname,
    (avg(cs.exam_season_one)   avg(cs.exam_season_two)   
    avg(cs.degree_season_one)   avg(cs.degree_season_two)) / 4 as av
  FROM courses_student cs
  JOIN students c on s.student_id = cs.student_id
  GROUP BY s.studentname
) x

It doesn't look shorter, but it's much easier to spot a mistake and to fix it.

CodePudding user response：

You can try CTE like this:

With CTE( SELECT students.studentname, AVG(courses_student.exam_season_one) AVG(courses_student.exam_season_two) AVG(courses_student.degree_season_one) AVG(courses_student.degree_season_two)) / 4 As average FROM courses_student JOIN students ON students.student_id = courses_student.student_id GROUP BY students.studentname ) select studentname, case when average >= 80 then 'EXCELLENT' when average >= 70 then 'VERY GOOD' when average >= 60 then 'GOOD' when average >= 50 then 'ACCEPTABLE' else 'FAIL' end as grade from CTE