CODEWITHSUNDEEP
mysqlsql
Govinda Sakhare
Govinda Sakhare

Reputation: 5726

SQL: count based on multiple columns

I have a table with following structure.

Flight_ID      |     Source_city    |    DestinatinCity
    1                  NYC                  LONDON
    2                 LONDON                TOKYO
    3                 LONDON                 NYC

I want to found city count i.e cities involved in source and destination,

city      | count
LONDON        3
NYC           2
TOKYO         1

How can I solve it using basic SQL features(Without PL).

Upvotes: 1

Views: 50

Answers (2)

Tom Taylor
Tom Taylor

Reputation: 3570

You can achieve it with union all 

select source_city as city, count(*) from table_name union all select dest_city,count(*) from table_name group by source_city;

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use union all and aggregation.

select city,count(*) as cnt
from (
select flight_Id,source_city as city from t
union all 
select flight_Id,destination_city from t
) x
group by city

Upvotes: 3

Related Questions