Geni-sama Geni-sama - 29 days ago 6
SQL Question

How can I assign long expressions inside SELECT?

How can I not repeat the usage of avg(examinee_grade_science + ...)?
As you can see I repeated it under the case command.
Can I assign it to a variable and use it in the Case function?
I tried using SET (set @ave_grade = examine_grade...) but it's not working.
Is there anyway it could be possible?

select
examinee_grade_science as "Science Grade",
examinee_grade_math as "Math Grade",
examinee_grade_literature as "Literature Grade",
examinee_grade_science + examinee_grade_math + examinee_grade_literature as "TOTAL",
avg(examinee_grade_science + examinee_grade_math + examinee_grade_literature) as "AVERAGE",


case when avg(examinee_grade_science + examinee_grade_math + examinee_grade_literature) < 93
then 'PASSED'
else 'FAILED'
end as "REMARKS"


from examinee

Answer

Use a subquery to calculate average and then use the alias assigned to average calculkated in outer query to check for Pass/Fail

SELECT *,
 case when AVERAGE < 93 
                    then 'PASSED' 
                    else 'FAILED' 
              end as "REMARKS"
FROM
(
    select  
                  examinee_grade_science as "Science Grade", 
                  examinee_grade_math as "Math Grade", 
                  examinee_grade_literature as "Literature Grade",
                  examinee_grade_science + examinee_grade_math +  examinee_grade_literature as "TOTAL",
                  avg(examinee_grade_science + examinee_grade_math + examinee_grade_literature) as "AVERAGE"



    from examinee
) t