SQL: count based on multiple columns

Question

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).

What's meany by PL?

Tom Taylor
– Tom Taylor

2017-03-22 15:22:45 +00:00
Commented Mar 22, 2017 at 15:22 — Tom Taylor
– Tom Taylor, Commented Mar 22, 2017 at 15:22
@RajasubaSubramanian May be Stored Procedures

Tom J Muthirenthi
– Tom J Muthirenthi

2017-03-22 15:48:37 +00:00
Commented Mar 22, 2017 at 15:48 — Tom J Muthirenthi
– Tom J Muthirenthi, Commented Mar 22, 2017 at 15:48

Vamsi Prabhala · Accepted Answer · 2017-03-22 15:23:23Z

3

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

answered Mar 22, 2017 at 15:23

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Taylor · Accepted Answer · 2017-03-26 09:45:26Z

0

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;

edited Mar 26, 2017 at 9:45

answered Mar 22, 2017 at 15:36

Tom Taylor

3,6182 gold badges41 silver badges75 bronze badges

Collectives™ on Stack Overflow

SQL: count based on multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related