I have a table which shows arrival airport and departure airport of flights. I need to find arrivals and departures of every airport
First one is the given table and second is the required table
I have tried the following
SELECT T.departure, count(F.departure), count(F.arrival)
FROM (
SELECT departure FROM flights
UNION
SELECT arrival FROM flights
) T
LEFT JOIN flights F ON T.departure = F.departure
LEFT JOIN flights F2 ON T.departure = F.arrival
GROUP BY T.departure