SQL Ifelse by Group

Question

I have SQL table such as this:

TABLE1
STUDENT SCORE   SCOREVAL
1   PASS    10
1   MOD1    4
1   MOD2    5
2   MOD1    4
3   MOD2    2
3   MOD2    1
3   MOD1    9
3   ER  1
4   MOD2    10
4   ER  6

I wish to simplify table to this

STUDENT SCORE
STUDENT SCORE   SCOREVAL
1   PASS    10
2   MOD1    4
3   PASS    11
4   MOD2    10

using the rules

For each STUDENT
if any value of **SCORE** equals to PASS, then is PASS
if contains value MOD1 and MOD2, then is PASS
if contains value MOD1, then is MOD1
if contains value MOD2, then is MOD2

Then for SCOREVAL,

if any value of SCORE equals to PASS, then take MAX(SCOREVAL[SCORE = PASS])
if contains value MOD1 and MOD2, then take MAX(SCOREVAL[SCORE = MOD1] + MAX(SCOREVAL[SCORE=MOD2])
if contains value MOD1, then take MAX(SCOREVAL[SCORE = MOD1])
if contains value MOD2, then take MAX(SCOREVAL[SCORE = MOD2])

I try this:

SELECT * 
FROM TABLE1 
GROUP BY STUDENT when SCORE = 'PASS' then 'PASS'
when SCORE = 'MOD1' and SCORE = 'MOD2' then 'PASS'
when SCORE = 'MOD1' then 'MOD1'
when SCORE = 'MOD2' then 'MOD2'
else EXEMPT
end

without success

Answer 1

I changed the code so that the SQL query groups the data by STUDENT, then determines each student's final SCORE and SCOREVAL based on specific conditions using CASE statements and aggregate functions like MAX(). Hope it helps!

SELECT 
    STUDENT, 
    CASE 
        WHEN MAX(SCORE = 'PASS') = 1 THEN 'PASS'
        WHEN MAX(SCORE = 'MOD1') = 1 AND MAX(SCORE = 'MOD2') = 1 THEN 'PASS'
        WHEN MAX(SCORE = 'MOD1') = 1 THEN 'MOD1'
        WHEN MAX(SCORE = 'MOD2') = 1 THEN 'MOD2'
        ELSE 'EXEMPT'
    END AS SCORE,
    CASE 
        WHEN MAX(SCORE = 'PASS') = 1 THEN MAX(CASE WHEN SCORE = 'PASS' THEN SCOREVAL END)
        WHEN MAX(SCORE = 'MOD1') = 1 AND MAX(SCORE = 'MOD2') = 1 THEN MAX(CASE WHEN SCORE = 'MOD1' THEN SCOREVAL END) + MAX(CASE WHEN SCORE = 'MOD2' THEN SCOREVAL END)
        WHEN MAX(SCORE = 'MOD1') = 1 THEN MAX(CASE WHEN SCORE = 'MOD1' THEN SCOREVAL END)
        WHEN MAX(SCORE = 'MOD2') = 1 THEN MAX(CASE WHEN SCORE = 'MOD2' THEN SCOREVAL END)
    END AS SCOREVAL
FROM TABLE1
GROUP BY STUDENT;