Using Max with CASE WHEN in mysql-CodePudding

When do we use max with case when in mysql ?

If I want to output the score for each of the course and return null if there's no value, the following query does not fully output all the values in each columns max function is used. Why is adding the max function necessary for this case ?

SELECT
sc.s_id, 
CASE WHEN sc.c_id = '02' THEN sc.s_score ELSE NULL END 'Course1' ,  
CASE WHEN sc.c_id = '01' THEN sc.s_score ELSE NULL END 'Course2' ,
CASE WHEN sc.c_id = '03' THEN sc.s_score ELSE NULL END 'Course3' ,
FROM score sc 
GROUP BY sc.s_id 

SELECT
sc.s_id, 
MAX(CASE WHEN sc.c_id = '02' THEN sc.s_score ELSE NULL END) 'Course1' ,  
MAX(CASE WHEN sc.c_id = '01' THEN sc.s_score ELSE NULL END) 'Course2' ,
MAX(CASE WHEN sc.c_id = '03' THEN sc.s_score ELSE NULL END) 'Course3' ,
FROM score sc 
GROUP BY sc.s_id

CodePudding user response：

When do we use max with case when in mysql ?

To finish off an operation that rotates data from vertical to horizontal

Take a look at this vertical data:

FRUIT
-----  
apple 
orange
pear

If we write this:

SELECT
  CASE WHEN fruit = 'apple' THEN fruit END,
  CASE WHEN fruit = 'orange' THEN fruit END,
  CASE WHEN fruit = 'pear' THEN fruit END
FROM t

^{(Yes it's a bit pointless to use the same column in the WHEN and the THEN but it doesn't matter for explaining this concept)}

We get this result:

apple NULL   NULL
NULL  orange NULL
NULL  NULL   pear

The data has gone from being vertical to diagonal - it has "rotated 45 degrees"

To complete the rotation, some aggregate like MIN or MAX can be used. Aggregating eliminates null because any value is worth more than a null. Faced with a choice between NULL and 'apple', even MIN will always choose 'apple'.

The aggregation goes outside (wraps round) the CASE WHEN

SELECT
  MAX(CASE WHEN fruit = 'apple' THEN fruit END),
  MAX(CASE WHEN fruit = 'orange' THEN fruit END),
  MAX(CASE WHEN fruit = 'pear' THEN fruit END)
FROM t

This gives a result of:

apple  orange  pear

The data has rotated 90 degrees, from vertical to horizontal. What was rows before is now columns

Typically you'll put some other column(s) in too, and group by it(them). The unique values from this grouped column(s) give the row headers. These remain variable in number, but the column count is fixed and driven by the number of MAX CASE WHEN you have.

You choose an aggregate operation that suits the kind of summarizing you want the grid cells to have. If it's text data then it'll usually be min or max, if it's numeric and there is more than one value per key (row header) you'll perhaps choose count, sum, avg etc

In summary

The CASE WHEN spreads a column of data out across multiple columns making it diagonal
The MAX compresses the diagonal data to one row, completing the rotation from vertical to horizontal
Thease operations must be done in "vertical, diagonal, horizontal" order hence why the MAX(CASE WHEN..) so the case when is done first

CodePudding user response：

Check this query

SELECT
sc.s_id, 
CASE WHEN sc.c_id = '02' THEN MAX(sc.s_score) ELSE NULL END 'Course1' ,  
CASE WHEN sc.c_id = '01' THEN MAX(sc.s_score) ELSE NULL END 'Course2' ,
CASE WHEN sc.c_id = '03' THEN MAX(sc.s_score) ELSE NULL END 'Course3' 
FROM score sc 
GROUP BY sc.s_id