How to get count of two-way combinations from two columns?

Question

I work for a trucking company, and we're interested in getting a count of the number of times one of our trucks travels between two cities, in either direction.

I have a table that lists an origin and destination for each trip segment, such as:

Origin    Destination

City 1    City 2
City 2    City 1
City 3    City 4
City 2    City 1

I need a query that tells me that there were three trips between City 1 and City 2 and one trip between City 3 and City 4. Thanks very much!

Answer 1

I think the following should do the trick.

SELECT route , COUNT(1) FROM 
(
   SELECT 
   CASE WHEN Origin > Destination THEN Origin+'_'+Destination
   ELSE Destination+'_'+Origin 
   END AS route
   FROM table1
 )a
 GROUP BY route