how to add a temp values for query mysql-CodePudding

i have the following query in my DB and i want to add a new column that handle the final Apperciation for the student: the query:

select student_name,q4.percentage
  from (select q2.student_id,mark *100/total as Percentage
          from (select class_id,sum(max_mark)as total
                  from course
                 group by(class_id)
               )q1 ,
               (select sum(mark) as mark,student_id
                  from grades
                 group by(student_id)
               ) q2
         where q2.student_id in (select student_id
                                   from student
                                  where student.section_id in(select section_id 
                                                                from section 
                                                               where class_id=q1.class_id)
                                )
         order by q2.student_id
       ) q4
inner join student on q4.student_id=student.student_id;

here's the result : enter image description here

now i just need to add a new column to the result like an award so the table is like:

student_name     percetage award 
mahmoud-kabbani  79.166667 B
Kareem-Alshaeer  54.583    c

CodePudding user response：

You can include 1 column with case statement like below -

select student_name,q4.percentage,
       CASE WHEN q4.percentage > 80 THEN 'A'
            WHEN q4.percentage > 60 THEN 'B'
            WHEN q4.percentage > 40 THEN 'C'
            ELSE 'E'
       END award
  from (select q2.student_id,mark *100/total as Percentage
          from (select class_id,sum(max_mark)as total
                  from course
                 group by(class_id)
               )q1 ,
               (select sum(mark) as mark,student_id
                  from grades
                 group by(student_id)
               ) q2
         where q2.student_id in (select student_id
                                   from student
                                  where student.section_id in(select section_id 
                                                                from section 
                                                               where class_id=q1.class_id)
                                )
         order by q2.student_id
       ) q4
inner join student on q4.student_id=student.student_id;