CODEWITHSUNDEEP
mysqlsql
Kiksy
Kiksy

Reputation: 1292

MySQL query GROUP BY WHERE IN

I have a table like:

+------+--------+-----------+
|      | name   | type_id   |
+------+--------+-----------+
|    1 | Bob    | 3         |
|    2 | Tony   | 2         |
|    3 | Sheila | 2         |
|    4 | Sarah  | 8         |
|    5 | Tom    | 7         |
+------+--------+-----------+

and I want to group my type_id into a new column, called 'type' . type_id 2 and 8 would have the value 'yes', everything else 'no' so my results would look something like:

+------+--------+-----------+------+
|      | name   | type_id   | type |
+------+--------+-----------+------+
|    1 | Bob    | 3         | no   |
|    2 | Tony   | 2         | yes  |
|    3 | Sheila | 2         | yes  |
|    4 | Sarah  | 8         | yes  |
|    5 | Tom    | 7         | no   |
+------+--------+-----------+------+

Is this even possible, if so whats it called, as I've searched the docs on 'GROUP BY' and 'JOINS' , but couldn't see a solution like this.

Upvotes: 2

Views: 125

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

You can do that with a case:

SELECT *,CASE WHEN type_id IN (2,8) THEN 'yes' ELSE 'no' END as `type`
FROM yourTable

sqlfiddle demo

Upvotes: 6

Related Questions