Hi, I'm working on a SQL project and developed a query to calculate the distance between trip commutes. I'm trying to determine how to calculate mileage for the distance column. Can anyone assist me with this issue?
--Find the average distance between each station, if the each trip starts at one station and ends at a different station. SELECT id, b.user_type, name AS start_station, Sum( sqrt( Power( ( Select latitude from capitalbikeshare_stations WHERE capitalbikeshare_stations.id = B.end_station_id )- S.latitude, 2 )+ power( ( Select longitude from capitalbikeshare_stations Where capitalbikeshare_stations.id = B.end_station_id )- S.longitude, 2 ) ) ) / Count(*) AS distance FROM capitalbikeshare_stations as S Inner Join capitalbikeshare_2019 as B On S.id = B.start_station_id Where start_station_id <> end_station_id GROUP BY id, b.user_type ORDER BY distance DESC;