CODEWITHSUNDEEP
mysqlsql
usr30911
usr30911

Reputation: 2791

MySQL query multiple column group by issue

This is my data set

enter image description here

I want to get the most common used phone and car per city. So the result expected is

enter image description here

For example: in NewYork there are 2 instances of iPhone out of 3 and 2 instances of Honda car out of 3, so they are the most common used phone and car for NY.

I'm not able to express in query how to execute group by clause on these 3 columns simultaneously.

Any pointers are appreciated, thanks in advance.

Upvotes: 1

Views: 64

Answers (1)

The Impaler
The Impaler

Reputation: 48750

I'm sure there's a shorter way but, anyway, you can do:

select x.city, x.phone, y.car
from (
  select city, phone, row_number() over(partition by city order by count(*) desc) as rn
  from t group by city, phone
) x
join (
  select city, car, row_number() over(partition by city order by count(*) desc) as rn
  from t group by city, car
) y on x.city = y.city and x.rn = 1 and y.rn = 1

Result:

 city      phone    car   
 --------- -------- ----- 
 Houston   Android  Kia   
 New York  iPhone   Honda

See running example at db<>fiddle.

Upvotes: 1

Related Questions