How can i make this query better ? Is this the correct use of the CASE statement?

Question

Among the students registered for the course "Psychology". what % of them have a GPA > 3?

Student:

student_id* | student_name | student_gender

Course:

course_id* | course_name | course_type

Student_course_grade:

student_id | course_id | grade

Please note :

Grade field in Student_course_grade table is a number in (5,4,3,2,1) instead of a letter grade like (A,B,C,D,E)
For a student who has registered for a course and has not completed it yet, the grade will be null.
GPA= Grade Point Average ( average of all grades scored by the student)

Ans:

Select 100*count(case when avg(b.grade) >3 and b.course_name = ‘Psychology’ then 1 else 0)/count(Case when b.course_name = ‘Psychology’ then 1 else O)
From course a left join
     student_course_grade b
     On a.courseid=b.courseid Join
     student c
     On c.studentid=b.student.id
Where b.grade is NULL

Hi, Welcome to stackoverflow! To help us help you better, it would be great if you could provide a code sample or point out which issues you are having trying to solve your problem. You might want to check this page out: https://stackoverflow.com/help/how-to-ask — David, Oct 13 '19 at 04:28
@Joe . . . Your code is not correct. Your `where` filters only to rows that have no `grade` but you are trying to do a calculation on the column. Further, you have `avg()` -- without a `group by` that looks suspicious. — Gordon Linoff, Oct 13 '19 at 13:17

score 0 · Answer 1 · answered Oct 13 '19 at 14:05

Getting GPA itself has nothing to do with a single specific class. Think of them individually, then join to get result. Ex: Your GPA > 3 is exclusively on ALL courses a person took. The Psychology class just happens to come along for the ride as a flag. As soon as you apply a where condition to just Psychology, you would be missing the rest of the classes. As such, I am getting the average on the grades, but a MAX( case/when ) to capture IF any were the psych class. This way I am getting all class grades first-and-foremost.

select
      s.Student_Name,
      s.Student_Gender,
      AllGPA.GPA
   from
      ( select
              cg.Student_ID,
              avg(cg.grade) GPA,
              max( case when c.course_name = 'Psychology' then 1 else 0 end ) TookPsychClass
           from
              Course_Grade cg
                 JOIN Course c
                    on cg.course_id = c.course_id
           group by
              cg.Student_ID 
           having 
              avg( cg.grade ) > 3
              and max( case when c.course_name = 'Psychology' then 1 else 0 end )  = 1
 ) AllGPA
         JOIN Student S
            on AllGPA.Student_ID = S.StudentID

How can i make this query better ? Is this the correct use of the CASE statement?

1 Answers1