CODEWITHSUNDEEP
mysqlgroup-by
Armin Hierstetter
Armin Hierstetter

Reputation: 1098

Combine multiple rows in one GROUP BY

I have a table games with information about computer games. One aspect is the region where the game was released. Some games have been released in multiple regions, that's why there are three regional columns: region1, region2, region3.

I would like to GROUP a selection of games in that way that - no matter in which of those three regions columns the same region is placed - I will end up with one list of distinct regions.

Very basic example of the table in question:

id | name_of_game | region1 | region2 | region3 
-----------------------------------------------
1    Phoenix        us        fr        de
2    Scramble       fr        uk
3    Defender       ru

I want to get the following output

region
-------
us
fr
de
uk
ru

I struggle to come up with a solution.

Upvotes: 1

Views: 47

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You could use union for this

select region1 as region from table
union 
select region2 as region from table
union 
select region3 as region from table

To get the count of games for each region you can use following

select region,count(*)
from(
  select region1 as region,name_of_game from demo where region1 is not null
  union 
  select region2 as region,name_of_game from demo where region2 is not null
  union 
  select region3 as region,name_of_game from demo where region3 is not null
) t
group by region

DEMO

Upvotes: 2

Related Questions