How to find the average distance between the locations

I have a table with source, destination and distance between the locations like as below

i want to calculate average distance between the locations like, for example if we take A to B route 1: 21 miles, route 2: 28 miles, route 3: 19 miles I am expecting results: A to B –> 22.66 miles Thanks

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  LEAST(source, destination) source, 
  GREATEST(source, destination) destination, 
  AVG(distance) distance 
FROM `project.dataset.table`
GROUP BY source, destination

You can test, play with above using sample data from your question as in example below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' source, 'b' destination, 21 distance UNION ALL
  SELECT 'b', 'a', 28 UNION ALL
  SELECT 'a', 'b', 19 UNION ALL
  SELECT 'c', 'd', 15 UNION ALL
  SELECT 'c', 'd', 17 UNION ALL
  SELECT 'd', 'c', 16.5 UNION ALL
  SELECT 'd', 'c', 18 
)
SELECT 
  LEAST(source, destination) source, 
  GREATEST(source, destination) destination, 
  AVG(distance) distance 
FROM `project.dataset.table`
GROUP BY source, destination

with result

Row source  destination distance     
1   a       b           22.666666666666668   
2   c       d           16.625