SQL Group by - Aggregate a column based on custom logic

Question

I have come across a case where I need to aggregate the table based on custom logic.

Student_id	Subject	Result
123	Maths	FAILED
123	English	PASS
456	Maths	PASS
456	English	PASS

Now I want the result in the following way:

Student_id	overall_result
123	FAILED ( If a student failed in any subject, overall he is failed)
456	PASS

Any idea how can I do this using SQL?

EDITED: Here can be other values also like GRACE , SUPPLYMENT. A student is passed only and only when all values are PASS, otherwise FAILED

Answer 1

If your field "Result" allows only "PASSED" and "FAILED" values, you can use the aggregate function MIN to retrieve the smallest value between the two. If there's at least one "FAILED", it will get returned.

SELECT Student_id, MIN(Result)
FROM tab
GROUP BY Student_id

Check the demo here.

---

If you can have multiple values too, you can enforce any value different than "PASSED" to "FAILED", then get the MIN as per previous query.

SELECT Student_id, 
       MIN(CASE WHEN Result = 'PASS' THEN 'PASS'
                ELSE 'FAILED' 
           END) AS Result
FROM tab
GROUP BY Student_id

Check the demo here.