mysql - sql group by different columns and count on different columns

Question

I have table structure like below.

mode       ogn_city    dst_city      city 

flight     bengaluru    delhi
flight     kolkata      bengaluru
flight     delhi        mumbai
hotel                               mumbai
flight     mumbai       delhi
hotel                               kanpur
hotel                               bengaluru

need to get data on cities wise and the mode(both) count of that cities
required output is like below

citi        flight_count   hotel_count
bengaluru     2             1
kolkata       1             0
delhi         2             0
mumbai        2             1
kanpur        0             1

I am able to get only one count but not both.

question from:https://stackoverflow.com/questions/65831380/sql-group-by-different-columns-and-count-on-different-columns

Answer 1

You can unpivot and aggregate:

select city, sum(flight), sum(hotel)
from ((select ogn_city as city, 1 as flight, 0 as hotel
       from t
      ) union all
      (select dst_city, 1, 0
       from t
      ) union all
      (select city, 0, 1
       from t
      ) 
     ) t
where city is not null;
group by city;

Note: The above uses the contents of the columns to determine if they are included. You can also use mode instead:

select city, sum(flight), sum(hotel)
from ((select ogn_city as city, 1 as flight, 0 as hotel
       from t
       where mode = 'flight'
      ) union all
      (select dst_city, 1, 0
       from t
       where mode = 'flight'
      ) union all
      (select city, 0, 1
       from t
       where mode = 'hotel'
      ) 
     ) t
group by city;