CODEWITHSUNDEEP
mysqlsql
Lkiia
Lkiia

Reputation: 3

Using CASE and GROUP BY operator

I have a score table like this -

student_id course_id score
01 01 80
01 02 90
01 03 99
02 02 70
02 03 60

I want to generate a table showing a student's scores in one row, and if there is no score, show it as NULL. Like this -

student_id 01 02 03
01 80 90 99
02 NULL 70 60

The code that I came up with is -

SELECT 
    student_id, 
    (CASE WHEN course_id = '01' THEN score ELSE NULL END) AS '01', 
    (CASE WHEN course_id = '02' THEN score ELSE NULL END) AS '02', 
    (CASE WHEN course_id = '03' THEN score ELSE NULL END) AS '03'
FROM table;

And as expected I got three scores in separate rows. Like this -

student_id 01 02 03
01 80 NULL NULL
01 NULL 90 NULL
01 NULL NULL 99
02 NULL 70 NULL
02 NULL NULL 60

The answer says I need to use MAX in front of the CASE statement. Also GROUP BY student_id at the end, or the student with NULL score value will not be shown in the result. Like this -

SELECT 
    student_id, 
    MAX(CASE WHEN course_id = '01' THEN score ELSE NULL END) AS '01', 
    MAX(CASE WHEN course_id = '02' THEN score ELSE NULL END) AS '02', 
    MAX(CASE WHEN course_id = '03' THEN score ELSE NULL END) AS '03',
FROM table
GROUP BY student_id;

My questions are:

  1. How does MAX combine three rows each with one score, into one row with three scores? What happened here?
  2. If I do not use GROUP BY student_id at the end, the result will exclude the student with NULL score value. Why? How does GROUP BY include the student back?

Any help would be appreciated! :)

Upvotes: 0

Views: 377

Answers (3)

lemon
lemon

Reputation: 15492

This kind of operation is called "Pivoting", and it uses two steps:

  • "enlarging the schema", by adding more columns to fit the maximum number of potential values that a column can have (in your specific case, the column is "score"). This operation just affects the amount of columns, while keeping the amount of rows same as the original table. Output for the single id
  • "aggregating the rows", necessary step as long as we have already distributed the values of your column in different numbered columns. This operation just affects the amount of rows, while keeping the amount of columns same as the output of the previous step. Output for the single id

So how do you do each of these two steps?

In order to "enlarge the schema", you require CASE statements (how many? the amount equals the maximum amount of values you want to extract for each id), which will extract only one value per row, for each id. Given that each new CASE column will have one non-NULL and others NULL-only values (for each id), then the MAX aggregation function comes in handy, and it will select the maximum value between one non-null value and all other NULL values (which results in selecting the non-null value). In order to limit the range of action of the MAX aggregation function to each specific id, we use the GROUP BY, which tells the DBMS to select the maximum value for each field(s) specified in its definition (for this task, only "student_id").

Hopefully this will answer both your doubts regarding how this way of solving pivoting problems work.

Upvotes: 0

Stu
Stu

Reputation: 32619

With your intermediate results, you need to return one row per student_id which is where the group by comes in, it collects all rows together for each unique value of the grouped columns.

On its own that wouldn't get you anywhere, you also need to tell SQL what to do with the other non-grouped columns, which is where max() comes in.

This returns the maximum value per group (i.e. per each unique Student_Id) and automatically excludes NULL values; since there is only one value other than NULL in each column, the required value is returned. Note you can equally use other aggregate functions eg Min() - the end result is the same.

Also note you can drop the else null part as NULL is the default when not matched.

Upvotes: 0

nbk
nbk

Reputation: 49385

GROUP By will produce a result set with one row for every student_id.

The MAX will garantue that the higest Value will be in the row for everys student_id as you have one score and multiple NULLs.

To get all Studnent_ids you need to LEFT JOIN all students, so ou have all.

SELECT
 t1.student_id,
COALESCE(`01`,0) as '01',
COALESCE(`02`,0) as '02',
COALESCE(`03`,0) as '03'
FROM
(SELECT DISTINCT student_id   FROM score) t1 
LEFT JOIN (SELECT 
    student_id, 
    MAX(CASE WHEN course_id = '01' THEN score ELSE NULL END) AS '01', 
    MAX(CASE WHEN course_id = '02' THEN score ELSE NULL END) AS '02', 
    MAX(CASE WHEN course_id = '03' THEN score ELSE NULL END) AS '03',
FROM table
GROUP BY student_id) t2 ON t1.student_id = t2.student_id

Upvotes: 0

Related Questions