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
Here's one option:
SQL> with
2 test (dep, arr) as
3 -- your sample data
4 (select 'a', 'e' from dual union all
5 select 'a', 'e' from dual union all
6 select 'a', 'e' from dual union all
7 select 'b', 'f' from dual union all
8 select 'b', 'f' from dual union all
9 select 'b', 'g' from dual union all
10 select 'c', 'g' from dual
11 ),
12 deparr as
13 -- departures and arrivals
14 (select dep airport, 1 departure, 0 arrival from test
15 union all
16 select arr airport, 0 departure, 1 arrival from test
17 )
18 select airport, sum(departure) departure, sum(arrival) arrival
19 from deparr
20 group by airport
21 order by airport;
A DEPARTURE ARRIVAL
- ---------- ----------
a 3 0
b 3 0
c 1 0
e 0 3
f 0 2
g 0 2
6 rows selected.
SQL>
(By the way, your expected output seems to be wrong. B has 3 departures.)