'SQL Find Most Frequent Route

Departure Destination FlightCount
A B 5
B A 4
A C 7
C A 1

I need to find the most frequent flight regardless of the flight directions from the above table.

For example, instead of A-C route having the most flight count of 7, I want the result to show A-B/B-A which have total flight count of 9.

I hope this makes sense.

Thank you

sql


Solution 1:[1]

You can standardize the order of departure and destination by ordering them lexicographically using least and greatest:

WITH standardized AS (
    SELECT least(departure, destination) location1,
           greatest(departure, destination) location2
      FROM flight
)
SELECT location1,
       location2, 
       count(*) FlightCount
  FROM standardized
 GROUP BY location1, location2

Output:

location1 location2 FlightCount
A B 9
A C 8

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 flwd

Related Questions