Search code examples
sqlaveragedifference

Average difference between values SQL

I'm trying to find the difference between values using SQL where the second value is always larger than the previous value.

Example Data: 

Car_ID | Trip_ID | Mileage
1       1         10,000
1       2         11,000
1       3         11,500
2       1         5,000
2       2         7,000
2       3         8,000

Expect Calculation:

Car_ID: 1
(Trip 2 - Trip 1) = 1,000 
(Trip 3 - Trip 2) = 500
Average Difference: 750

Car_ID: 2
(Trip 2 - Trip 1) = 2,000 
(Trip 3 - Trip 2) = 1,000
Average Difference: 1,500

Expected Output: 

Car_ID | Average_Difference
1        750 
2        1,500
Solution

  • You can use aggregation:

    select car_id,
       (max(mileage) - min(mileage)) / nullif(count(*) - 1, 0)
from t
group by car_id;

    That is, the average as you have defined it is the maximum minus the minimum divided by one less than the number of trips.