admin@onlinelearningcenter.in (+91) 7 999 01 02 03

Total Travel hours in SQL

Ayush Dixit
15 Posts

You have been given a table called travel_detail and you need to calculate the total travel hours between the cities.


For example, the total travel hours between Delhi and Goa is 235 (i.e 125+110)


Write an SQL query to calculate the total travel hours between cities

 

Sample Input:-

 

Sample Output:-

 

My Solution:-

We have to consider the total travel hours between City1 to City2 and City2 to City1 as one single pair. So we will compare the City names with greater than and less than operator to swap the same side then we just have to calculate the total travel hours.

Final Query:-

WITH travel(city_l, city_2, travel_time_hours) AS

(

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 travel_detail


)

SELECT city_1, city_2,

SUM(travel_time_hours) AS total_travel_hours FROM travel GROUP BY city_1, city_2 ORDER BY city_1, city_2;

 

Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.

Published By : Ayush Dixit
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Comments