CODEWITHSUNDEEP
sqlsql-server
user8400129
user8400129

Reputation: 213

How to output a value refer to another value?

I have a table which including Student's name, course_id and score, the different students may have a different course and different score.

And now I need to output the academic standings for each student, here is the rule for the academic standings:

  1. If a student takes only one course(count(course_id)=1), he/she will receive ‘Good’ if the score >= 50, and ‘Referral’ if the score < 50;
  2. If a student takes more than one course (count(course_id)>=2), his/her academic standing would be ‘Probation’ if none of the score >=50, ‘Referral’ if 50% or less of the taken courses are score >=50, and ‘Good’ otherwise.

Table:

Student_name|   course_id |score
Shirley Caws      55993     10
Lana Glendenning  56988     81
Michael           54880     80
Michael           54895     71
Sean Turle        56986     32
Sean Turle        56991     48
Sean Turle        56992     20
Damchu Tenzin     56215     40
Damchu Tenzin     56219     90
Blake Croll       57179     30
Blake Croll       57264     20

I have tried for an hour in writing "CASE WHEN" but fail to get the right answer. SELECT student_name, (CASE WHEN .... THEN ELSE END) AS academic standings FROM table;

Expected results:

Student_name|    Academic_standings
Shirley Caws     Referral
Lana Glendenning Good
Michael          Good
Sean Turle       Probation
Damchu Tenzin    Referral
Blake Croll      Probation

Thanks!

Upvotes: 0

Views: 49

Answers (2)

APH
APH

Reputation: 4154

I'd try something like this - count the classes first, then apply your logic:

; with CTE as (
    Select StudentName
    , Count(distinct CourseID) as TotalClasses
    , Count(distinct case when score < 50 then CourseID end) as ClassesUnder50
    From MyTable
    Group By StudentName
)

Select StudentName
    , Case when TotalClasses = 1 and ClassesUnder50 = 0 then 'Good'
           when TotalClasses = 1 and ClassesUnder50 = 1 then 'Referral'
           when TotalClasses > 1 and ClassesUnder50 = TotalClasses then 'Probation'
           when TotalClasses > 1 and ClassesUnder50*1.0/TotalClasses >= 0.5 then 'Referral'
           else 'Good' end as Standing
from CTE

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use aggregation functions and conditional aggregation:

select student_name,
       (case when count(*) = 1 and max(score) >= 50 then 'Good'
             when count(*) = 1 then 'Referral'
             when max(score) < 50 then 'Probation'
             when sum(case when score >= 50 then 1 else -1 end) <= 0 then 'Referral'
             else 'Good'
        end) as academic_standing             
from t
group by student_name;

Upvotes: 0

Related Questions