CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Chris Patten
Chris Patten

Reputation: 129

Counting distinct values from multiple columns

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

Upvotes: 0

Views: 73

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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:

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 4

Related Questions