CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Feras
Feras

Reputation: 37

Is there a way I can shorten this query?

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)

Upvotes: 0

Views: 63

Answers (2)

The Impaler
The Impaler

Reputation: 48770

For clarity, testing, and debugging purposes you can avoid repeating the same long expression by including it in a 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.

Upvotes: 2

Ramakant Mishra
Ramakant Mishra

Reputation: 11

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

Upvotes: 1

Related Questions