How to get the score of two-way combinations of two columns?

Question

I work for a transport company, and we are interested in counting the number of times that one of our trucks travels between two cities in any direction.

I have a table that lists the source and destination for each trip segment, for example:

Origin destination

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

I need a request that tells me that there were three trips between City 1 and City 2 and one trip between City 3 and City 4. Thank you 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

Answer 2

, , . , - , Origin Destination, "". , concat a1ex07. , .

declare @table table (Origin varchar(16), Destination varchar(16))
insert into @table
values
('City 1','City 2'),
('City 2','City 1'),
('City 3','City 4'),
('City 2','City 1')

;with cte as(
select 
    case when Origin > Destination then Origin else Destination end as Origin
    ,case when Destination < Origin then Destination else Origin end as Destination
from
    @table)

select
    Origin
    ,Destination
    ,count(Origin + Destination) 
from 
    cte
group by
    Origin
    ,Destination

Answer 3

, , .

, , :

create table City
(
    id int primary key identity,
    name nvarchar(100) not null
)

create table TravelLog
(
    trip_id int primary key identity,
    origin int foreign key references City,
    destination int foreign key references City,
    check (origin <> destination)
)

, TravelLog:

alter table TravelLog
    add 
        low as case when origin <= destination then origin else destination end persisted,
        high as case when origin >= destination then origin else destination end persisted

, , :

with Q as
(
    select count (*) as trips, low, high from TravelLog group by low, high
)

select Q.trips, o.name point_A, d.name point_B from Q 
    inner join City o on o.id = Q.low
    inner join City d on d.id = Q.high

, ..