CODEWITHSUNDEEP
mysql
Ashish Mohan
Ashish Mohan

Reputation: 724

Finding total cost of road

I have a table with 3 columns source, dest, total cost and have values like

Agra Delhi 500
Agra Kanpur 400
Delhi Agra 900
Kanpur Agra 500

I want total cost like 

Agra<-->Delhi 1400
Agra<-->Kanpur 900

Upvotes: 0

Views: 44

Answers (2)

Chesser
Chesser

Reputation: 465

SELECT a.source, a.dest, (a.total_cost + b.total_cost) round_trip_cost
FROM my_tab as a 
    LEFT OUTER JOIN my_tab as b 
    ON (a.source = b.dest AND b.source = a.dest)

Upvotes: 2

fthiella
fthiella

Reputation: 49089

You can use LEAST() and GREATEST() functions, and a GROUP BY query:

SELECT
  LEAST(source, dest),
  GREATEST(source, dest),
  SUM(total_cost)
FROM
  tablename
GROUP BY
  LEAST(source, dest),
  GREATEST(source, dest)

Upvotes: 4

Related Questions