CODEWITHSUNDEEP
sqlsql-server
Muthukumar Palaniappan
Muthukumar Palaniappan

Reputation: 1670

How to case handle a field in Group by

I am trying to group the below CTE based on StudentID and I would like to compute the result with a rule like, if he passes in one subject he is passed overall , if not failed. 

WITH mycte(StudentId,SubjectId,Result) as
  (SELECT 1,1,'pass'
  UNION ALL SELECT 1,2,'fail'
  UNION ALL SELECT 1,3,'pass'
  UNION ALL SELECT 2,1,'fail'
  UNION ALL SELECT 2,2,'fail'
  UNION ALL SELECT 3,1,NULL
   )

Can you help me in understanding how to achieve this logic.

Expected Result is like

StudentID Result
1         pass
2         fail
3         NULL

Upvotes: 0

Views: 35

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Try this simple way

SELECT StudentId,
       Result = Max(Result)
FROM   mycte
GROUP  BY StudentId

Trick here is Pass will be ordered after Fail in string ordering. So when you use Max aggregate we will get Pass if at least one Pass is present

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You can aggregate over students and count the number of passes. According to your logic, if even one pass is seen then the student should be reckoned as passing overall.

SELECT DISTINCT
    t1.StudentId,
    t2.Result
FROM mycte t1
LEFT JOIN
(
    SELECT
        StudentId,
        CASE WHEN SUM(CASE WHEN Result = 'pass' THEN 1 ELSE 0 END) > 0
             THEN 'pass'
             WHEN SUM(CASE WHEN Result = 'fail' THEN 1 ELSE 0 END) > 0
             THEN 'fail'
             ELSE NULL END AS Result
    FROM mycte
    GROUP BY StudentId
) t2
    ON t1.StudentId = t2.StudentId

Upvotes: 2

Related Questions