CODEWITHSUNDEEP
sqlselect
Jannes Braet
Jannes Braet

Reputation: 11

Select the difference of two consecutive columns

I have a table car that looks like this:

| mileage | carid |
------------------
|   30    |   1   |
|   50    |   1   |
|   100   |   1   |
|   0     |   2   |
|   70    |   2   |

I would like to get the average difference for each car. So for example for car 1 I would like to get ((50-30)+(100-50))/2 = 35. So I created the following query

SELECT AVG(diff),carid FROM (
  SELECT (mileage-
    (SELECT Max(mileage) FROM car Where mileage<mileage AND carid=carid GROUP BY carid)) 
    AS diff,carid 
  FROM car GROUP BY carid)

But this doesn't work as I'm not able to use current row for the other column. And I'm quite clueless on how to actually solve this in a different way. So how would I be able to obtain the value of the next row somehow?

Upvotes: 0

Views: 131

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The average difference is the maximum minus he minimum divided by one less than the count (you can do the arithmetic to convince yourself this is true).

Hence:

select carid,
       ( (max(mileage) - min(mileage)) / nullif(count(*) - 1, 0)) as avg_diff
from cars
group by carid;

Upvotes: 2

Related Questions