I have a table of data that tracks the mileage of my beloved 2005 Toyota Prius.
+----+------------+---------+---------+-------+
| id | date | mileage | gallons | cost |
+----+------------+---------+---------+-------+
| 1 | 2005-02-14 | 280 | 8.615 | 16.03 |
| 2 | 2005-02-27 | 480 | 4.775 | 8.59 |
| 3 | 2005-03-19 | 713 | 7.213 | 14.27 |
| 4 | 2005-04-09 | 999 | 7.86 | 16.81 |
| 5 | 2005-04-11 | 1172 | 3.174 | 6.63 |
| 6 | 2005-05-05 | 1560 | 8.889 | 18.66 |
| 7 | 2005-06-07 | 1985 | 9.815 | 20.01 |
| 8 | 2005-07-03 | 2444 | 9.868 | 21.7 |
| 9 | 2005-08-13 | 2762 | 7.728 | 20 |
| 10 | 2005-09-11 | 3271 | 10.072 | 30.11 |
| 11 | 2005-10-24 | 3646 | 8.953 | 22.19 |
| 12 | 2005-10-30 | 3959 | 6.583 | 17.11 |
| 13 | 2005-11-04 | 4184 | 4.538 | 11.25 |
| 14 | 2005-11-21 | 4631 | 8.742 | 18 |
| 15 | 2005-12-31 | 4897 | 8.511 | 18.64 |
| 16 | 2006-02-02 | 5609 | 7.83 | 18 |
| 17 | 2006-03-01 | 6064 | 3.38 | 7.16 |
| 18 | 2006-03-13 | 6400 | 7.171 | 16.2 |
| 19 | 2006-03-22 | 6605 | 5.399 | 13.17 |
| 22 | 2006-02-16 | 5944 | 7.957 | 17.5 |
| 20 | 2006-04-06 | 6974 | 7.774 | 19.43 |
| 21 | 2006-04-23 | 7316 | 7.102 | 20.45 |
+----+------------+---------+---------+-------+
I wanted a way to calculate how many miles were driven for each trip, i.e. between consecutive dates, and what was the miles per gallon (MPG) for that trip. To do this I modified what I learned in this post, unique ID field, getting next and previous existing ID from table to create a view that does all these calculations. The advantage of this view is that it does not matter what order I enter the data, it will always calculate correctly.
CREATE VIEW v_prius_mpg AS
SELECT id, date, mileage, gallons, cost, mileage as trip_mileage,
mileage / gallons AS mpg, cost / gallons AS price_per_gallon
FROM prius_mpg
WHERE id = 1
UNION
SELECT New.id AS id, New.date, New.mileage, New.gallons, New.cost,
(New.mileage - Old.mileage) AS trip_mileage,
(New.mileage - Old.mileage)/ New.gallons AS mpg,
(New.cost / New.gallons) AS price_per_gallon
FROM prius_mpg New, prius_mpg Old
WHERE New.id > 1
AND Old.id =
(SELECT id
FROM prius_mpg
WHERE date < New.date
ORDER BY date DESC
LIMIT 1);
And here are the results of the view. Note how row 20 is out of order but it still calculates correctly the trip mileage and MPG.
+----+------------+---------+---------+--------------+------------------+
| id | date | mileage | gallons | trip_mileage | mpg |
+----+------------+---------+---------+--------------+------------------+
| 1 | 2005-02-14 | 280 | 8.615 | 280 | 32.501450957632 |
| 2 | 2005-02-27 | 480 | 4.775 | 200 | 41.8848167539267 |
| 3 | 2005-03-19 | 713 | 7.213 | 233 | 32.3027866352419 |
| 4 | 2005-04-09 | 999 | 7.86 | 286 | 36.3867684478371 |
| 5 | 2005-04-11 | 1172 | 3.174 | 173 | 54.5053560176434 |
| 6 | 2005-05-05 | 1560 | 8.889 | 388 | 43.6494543818202 |
| 7 | 2005-06-07 | 1985 | 9.815 | 425 | 43.301069791136 |
| 8 | 2005-07-03 | 2444 | 9.868 | 459 | 46.5139845966761 |
| 9 | 2005-08-13 | 2762 | 7.728 | 318 | 41.1490683229814 |
| 10 | 2005-09-11 | 3271 | 10.072 | 509 | 50.5361397934869 |
| 11 | 2005-10-24 | 3646 | 8.953 | 375 | 41.8854015413828 |
| 12 | 2005-10-30 | 3959 | 6.583 | 313 | 47.5467112258849 |
| 13 | 2005-11-04 | 4184 | 4.538 | 225 | 49.5813133539004 |
| 14 | 2005-11-21 | 4631 | 8.742 | 447 | 51.1324639670556 |
| 15 | 2005-12-31 | 4897 | 8.511 | 266 | 31.2536717189519 |
| 16 | 2006-02-02 | 5609 | 7.83 | 712 | 90.9323116219668 |
| 17 | 2006-03-01 | 6064 | 3.38 | 120 | 35.5029585798817 |
| 18 | 2006-03-13 | 6400 | 7.171 | 336 | 46.8553897643285 |
| 19 | 2006-03-22 | 6605 | 5.399 | 205 | 37.9699944434154 |
| 20 | 2006-02-16 | 5944 | 7.957 | 335 | 42.1012944577102 |
| 21 | 2006-04-06 | 6974 | 7.774 | 369 | 47.465912014407 |
| 22 | 2006-04-23 | 7316 | 7.102 | 342 | 48.1554491692481 |
+----+------------+---------+---------+--------------+------------------+
I always wanted to appear on every row how many gallons of gas had been consumed up to that day and how much had spent on gas up to date but I could not figure out how. The only thing I could do was determine the sums up until now.
mysql> select sum(gallons) as gallons_to_date,
sum(cost) as cost_to_date from prius_mpg where date <= current_date;
+-----------------+--------------+
| gallons_to_date | cost_to_date |
+-----------------+--------------+
| 161.949 | 371.91 |
+-----------------+--------------+
1 row in set (0.01 sec)