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)