SQL Group by - Aggregate a column based on custom logic-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

We can use COUNT or SUM with CASE.

If the sum of FAILED values is > 0, this means the overall result will be FAILED, otherwise it will be PASS.

SELECT Student_id, 
CASE 
  WHEN COUNT(CASE WHEN Result = 'FAILED' THEN 1 END) > 0 
    THEN 'FAILED' 
    ELSE 'PASS' END AS overall_result
FROM tab
GROUP BY Student_id;

If we use MySQL, the above query can be simplified to:

SELECT Student_id, 
CASE 
  WHEN SUM(Result = 'FAILED') > 0 
    THEN 'FAILED' 
    ELSE 'PASS' END AS overall_result
FROM tab
GROUP BY Student_id;

Tested here: db<>fiddle