Counting distinct values from multiple columns

Question

Given the following schema:

id departure arrival
0  BOS       LAX
1  SFO       SEA
2  MIA       LAX
3  RDU       BOS
4  JFK       DEN
5  LAX       SEA

I need to count the total occurrences of each airport. For example, BOS should be 2 (one departure and one arrival).

I'm able to do this with two separate queries:

SELECT departure, COUNT(*) FROM legs
GROUP BY departure ORDER BY COUNT(departure) DESC

and

SELECT arrival, COUNT(*) FROM legs
GROUP BY arrival ORDER BY COUNT(arrival) DESC

but I haven't been able to figure out or find a way to do it in one query. I'd like to have something like the following:

airport count
BOS     2
LAX     2
SEA     2
JFK     1

Giorgi Nakeuri · Accepted Answer · 2015-06-23T20:55:06.050

4

Do it with union:

select departure as airport, count(*) as count
from (select departure from legs
      union all
      select arrival from legs)t
group by departure

edited Jun 23 '15 at 20:55

answered Jun 23 '15 at 20:49

Giorgi Nakeuri

35,155
8
47
75

1

And to give the exact desired result, `departure as airport, count(*) as count` – Tom Jun 23 '15 at 20:51
1

Logically it would probably even make the most sense to add the "airport" alias inside the inner query. – shawnt00 Jun 23 '15 at 20:52

score 0 · Answer 2 · edited May 23 '17 at 12:29

Use a FULL [OUTER] JOIN on two separate aggregates:

SELECT airport, COALESCE(d.ct, 0) + COALESCE(a.ct, 0) AS "count"
FROM  (
   SELECT departure AS airport, count(*) AS ct
   FROM   legs
   GROUP  BY 1
   ) d
FULL JOIN (
   SELECT arrival AS airport, count(*) AS ct
   FROM   legs
   GROUP  BY 1
   ) a USING (airport)
ORDER  BY "count" DESC, airport;

This way you can easily return additional columns for arrival and departure, and you can use indexes on the base table if you should want to select certain airports.

Recent related answer:

Add up conditional counts on multiple columns of the same table

Counting distinct values from multiple columns

2 Answers2