How to calculate total travel hours between city x and y and vice versa?

reciprocal.sql

/*-------------------------------------------
2022-02-01
Rajanand Ilangovan
https://blog.rajanand.org/how-to-calculate-total-travel-hours-between-city-x-and-y-and-vice-versa
How to calculate total travel hours between city x and y and vice versa?
--------------------------------------------*/

USE demo;

if object_id (N'dbo.travel_detail', N'u') is not null 
drop table dbo.travel_detail
go


create table dbo.travel_detail (
	id int identity(1,1),
	from_city varchar(100),
	to_city varchar(100),
	travel_time_hours smallint
)
go

insert into dbo.travel_detail values
	('Oslo','Helsinki',125),
	('Helsinki','Oslo',110),
	('Stockholm','Oslo',132),
	('Oslo','Stockholm',180),
	('Copenhagen','Helsinki',148),
	('Helsinki','Copenhagen',84),
	('Stockholm','Copenhagen',116),
	('Helsinki','Stockholm',124)

go

select * from dbo.travel_detail


-- solution
select 
city_1,
city_2,
total_travel_hours = sum(travel_time_hours)
from (
	select 
	city_1 = case when from_city < to_city then from_city else to_city end,
	city_2 = case when from_city > to_city then from_city else to_city end,
	travel_time_hours
	from dbo.travel_detail
)x
group by city_1, city_2
order by city_1, city_2

--clean up
if object_id (N'dbo.travel_detail', N'u') is not null 
drop table dbo.travel_detail
go