SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Routes
( ID INT NOT NULL,
  ORIGIN VARCHAR(2) NOT NULL,
  DESTINATION VARCHAR(2) NOT NULL
);

 INSERT INTO Routes
   ( ID, ORIGIN, DESTINATION )
 VALUES 
   ( 1, 'AY', 'BW' ),
   ( 2, 'CA', 'DA' ),
   ( 3, 'BW', 'AY' ),
   ( 4, 'CA', 'AY' ),
   ( 5, 'DA', 'CA' ),
   ( 6, 'AZ', 'BW' ),
   ( 8, 'BW', 'AZ' );
7 rows affected

 SELECT *
 FROM Routes t1
 LEFT JOIN Routes t2
 ON   t2.Origin = t1.Destination
 AND  t2.Destination = t1.Origin;


ID
ORIGIN
DESTINATION
ID
ORIGIN
DESTINATION


1
AY
BW
3
BW
AY

2
CA
DA
5
DA
CA

3
BW
AY
1
AY
BW

4
CA
AY
null
null
null

5
DA
CA
2
CA
DA

6
AZ
BW
8
BW
AZ

8
BW
AZ
6
AZ
BW


-- this solution works fine
SELECT Route, COUNT(*) as Cnt
FROM
     (SELECT CASE 
               WHEN (t1.Origin < t1.Destination) OR t2.Origin IS NULL THEN 'Route ' + t1.Origin + t1.Destination
               ELSE 'Route ' + t1.Destination + t1.Origin
             END as Route
      FROM Routes t1
      LEFT JOIN Routes t2
      ON   t2.Origin = t1.Destination
      AND  t2.Destination = t1.Origin
     ) t1
GROUP BY Route
ORDER BY Route;


Route
Cnt


Route AYBW
2

Route AZBW
2

Route CAAY
1

Route CADA
2


SELECT  MIN(origin) AS point_1, 
        MAX(destination) AS point_2,
        COUNT(*) AS journey_count
FROM routes
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY 1, 2;


point_1
point_2
journey_count


AY
BW
4

CA
AY
1

CA
DA
2

ID	ORIGIN	DESTINATION	ID	ORIGIN	DESTINATION
1	AY	BW	3	BW	AY
2	CA	DA	5	DA	CA
3	BW	AY	1	AY	BW
4	CA	AY	null	null	null
5	DA	CA	2	CA	DA
6	AZ	BW	8	BW	AZ
8	BW	AZ	6	AZ	BW