Adam Adam - 1 month ago 5
MySQL Question

MySQL: Select multiple max values from one column

Suppose I got this table:

mytable


+------+-------+
| type | count |
+------+-------+
| red | 4 |
| blue | 3 |
| red | 2 |
| blue | 7 |
+------+-------+



Now I want this back:


+--------+---------+
| maxRed | maxBlue |
+--------+---------+
| 4 | 7 |
+--------+---------+



How can I do this?

This is what I tried

SELECT MAX(count) as maxRed, 0 as maxBlue FROM mytable WHERE type='red'

UNION

SELECT 0 as maxRed, MAX(count) as maxBlue FROM mytable WHERE type='blue'


but it does not quite work and only results


+--------+---------+
| maxRed | maxBlue |
+--------+---------+
| 4 | 0 |
| 0 | 7 |
+--------+---------+


Answer

This is how to do it:

SELECT
MAX(CASE WHEN type = 'red' THEN `count` ELSE NULL END) AS maxRed,
MAX(CASE WHEN type = 'blue' THEN `count` ELSE NULL END) AS maxBlue
FROM
mytable
WHERE type IN ('red', 'blue');