SQL Find Most Frequent Route-CodePudding

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

CodePudding user response：

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