How to find frequency of a value in two different columns in sql?

Question

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

Answer 1

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