How to calculate Cost per Mile

Formula

Cost per mile = total transport cost ÷ total miles driven

SQL query

This example assumes: trips(id, vehicle_id, trip_date, miles, fuel_cost, driver_cost, maintenance_cost). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT DATE_TRUNC('month', trip_date) AS month,
       SUM(fuel_cost + driver_cost + maintenance_cost) / NULLIF(SUM(miles), 0) AS cost_per_mile
FROM trips
GROUP BY 1
ORDER BY 1;

Common mistakes that make Cost per Mile wrong

• Cost and miles must come from the same date basis; a fuel invoice paid in March for February miles misaligns them.
• Empty (deadhead) miles are real cost; decide whether to include them.
• Missing maintenance cost makes a vehicle look cheaper.

Ask for Cost per Mile in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• What is our cost per mile this quarter versus last?
• Which vehicles cost the most per mile?