MySQL query multiple column group by issue-CodePudding

This is my data set

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

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.

CodePudding user response：

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.

CodePudding user response：

You could use some CTEs to do the math, and then join them:

with phone_counts as
(
    select City
        , Phone
        , row_number() over (partition by City order by count(*) desc) rn
    from tbl
    group by City, Phone
)
, car_counts as
(
    select City
        , Car
        , row_number() over (partition by City order by count(*) desc) rn
    from tbl
    group by City, Car
)
select distinct tbl.City
    , pc.Phone
    , cc.Car
from tbl
left join phone_counts pc
    on tbl.City = pc.City
    and pc.rn = 1
left join car_counts cc
    on tbl.City = cc.City
    and cc.rn = 1