Mysql - SQL Ifelse by Group

bvowe
December 14, 2023
176 views
0 votes
2 Answers

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

Answers

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;

Assuming you don’t ever want NULL values in the SCOREVAL column of your result set, you can consolidate some rules and build other rules into score calculations.

For example, if there is no PASS entry, then you can unconditionally sum the relative max scores for MOD1 and MOD2 entries. Coalesce any missing values to 0 to prevent a NULL value interfering with another valid entry.

Schema (MySQL v8.0)

CREATE TABLE TABLE1 (
    STUDENT INT,
    SCORE TEXT,
    SCOREVAL INT
);

INSERT INTO TABLE1 VALUES
(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),
(5, 'ER', 3),
(6, 'PASS', 8),
(6, 'PASS', 13),
(6, 'PASS', 7);

Query #1

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

STUDENT	SCORE	SCOREVAL
1	PASS	10
2	MOD1	4
3	PASS	11
4	MOD2	10
5	EXEMPT	0
6	PASS	13

View on DB Fiddle

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – SQL Ifelse by Group

Answers