Adam - 1 year ago 67

MySQL Question

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 Source

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');
```