SQL query to order sum of a column

Question

I have the following tables:

Drivers table, with a Driver_Code column

Route_files table, with a Driver_Code column and a Route_Code column

Routes table, with a Route_Code column and a Kilometers column

For every entry in the Drivers table there may be more than 1 entry in the Route_files table with the same Driver_Code. For every entry in the Route_files table, there is only one entry in the Routes table with the same Route_Code.

What I am trying to do is order the Drivers based on the total number of kilometers that they drove. So if I have the following data:

Drivers:

Driver_Code
2
3
4

Route_files:

Driver_Code Route_Code
2           20
2           50
2           30
3           30
4           40

Routes:

Route_Code Kilometers
20         1231
30         9
40         400000
50         24234

Then Driver 2 drove routes 20 30 and 50 so the total kilometers is 25474. Similarly driver 3 drove 9km and driver 4 drove 400000. The SQL query that I need should output:

Driver_Code Total_km
4           400000
2           25474
3           9

I tried to use an inner join on the Route_files and Routes tables to obtain a single "table" with all the necessary information, hoping that I could further use this obtained table, but couldn't figure out how to do that. I am working in dBase 2019(and can't change to something better, unfortunately). Any hints and ideas are appreciated!

Answer 1

I finally managed to do it. This is the working query:

select 
  Driver_Code, 
  SUM(km) as Total_km 
from 
  Route_files 
  inner join Routes on Route_files.Route_Code = Routes.Route_Codes 
GROUP BY 
  Route_files.Driver_Code 
ORDER BY 
  Total_km Descending

Initially I was doing select Driver_Code, km, SUM(km) and when trying to do GROUP BY, dBase was forcing me to group by Driver_Code as well as km, which meant that the SUM function was being applied to every single entry instead of on all the entries of a single Driver_Code, which is what I needed. Now I finally understand what GROUP BY does!

Thanks everyone for your comments!