SELECT YEAR(a.datestmp) 'Vuosi', SUM(IF(a.category='Capital',a.eur,0)) 'Pääoma', SUM(IF(a.category='Fuel',a.eur,0)) 'Polttoaine', SUM(IF(a.category='Insurance',a.eur,0)) 'Vakuutus', SUM(IF(a.category='Maintenance',a.eur,0)) 'Huolto', SUM(IF(a.category='Other',a.eur,0)) 'Muut', SUM(a.eur) 'Yhteensä', b.kmMax-b.kmMin 'Kilometrit', SUM(eur)/(b.kmMax-b.kmMin) 'EUR/km' FROM costs AS a LEFT JOIN ( SELECT YEAR(bb.datestmp) 'Vuosi', MIN(bb.km) 'kmMin', MAX(bb.km) 'kmMax' FROM costs AS bb WHERE bb.datestmp<=DATE(NOW()) AND km>0 GROUP BY YEAR(bb.datestmp) ) AS b ON YEAR(a.datestmp)=b.Vuosi WHERE a.datestmp<=DATE(NOW()) GROUP BY YEAR(a.datestmp); +-------+----------+------------+----------+--------+--------+-----------+------------+----------+ | Vuosi | Pääoma | Polttoaine | Vakuutus | Huolto | Muut | Yhteensä | Kilometrit | EUR/km | +-------+----------+------------+----------+--------+--------+-----------+------------+----------+ | 2016 | 8426.05 | 1229.41 | 397.34 | 199.00 | 178.77 | 10430.57 | 14568 | 0.715992 | | 2017 | 3427.32 | 1699.80 | 395.43 | 257.00 | 196.85 | 5976.40 | 19234 | 0.310721 | | 2018 | 3427.32 | 1117.47 | 703.92 | 359.35 | 119.40 | 5727.46 | 11634 | 0.492304 | | 2019 | 3427.32 | 0.00 | 0.00 | 0.00 | 0.00 | 3427.32 | NULL | NULL | | 2020 | 3427.32 | 0.00 | 0.00 | 0.00 | 0.00 | 3427.32 | NULL | NULL | +-------+----------+------------+----------+--------+--------+-----------+------------+----------+ SELECT datestmp, km, eur, liter, eur/liter 'EUR/l', km-(SELECT km FROM costs AS b WHERE b.km < a.km AND category='Fuel' ORDER BY km DESC LIMIT 1) 'km/tank', liter/((km-(SELECT km FROM costs AS b WHERE b.km < a.km AND category='Fuel' ORDER BY km DESC LIMIT 1))/100) 'l/100km' FROM costs AS a WHERE category='Fuel' ORDER BY km ASC; +------------+-------+-------+-------+----------+---------+----------+ | datestmp | km | eur | liter | EUR/l | km/tank | l/100km | +------------+-------+-------+-------+----------+---------+----------+ | 2016-01-07 | 48 | 48.58 | 36.28 | 1.339030 | NULL | NULL | | 2016-01-10 | 358 | 25.55 | 19.30 | 1.323834 | 310 | 6.225806 | | 2016-01-10 | 619 | 20.06 | 14.98 | 1.339119 | 261 | 5.739464 | | 2016-01-16 | 1141 | 40.59 | 31.25 | 1.298880 | 522 | 5.986590 | | 2016-01-19 | 1563 | 33.00 | 25.60 | 1.289063 | 422 | 6.066351 | | 2016-01-29 | 2110 | 42.69 | 32.61 | 1.309108 | 547 | 5.961609 | | 2016-02-18 | 2610 | 41.60 | 32.27 | 1.289123 | 500 | 6.454000 | | 2016-03-10 | 2961 | 32.00 | 24.35 | 1.314168 | 351 | 6.937322 | | 2016-03-14 | 3749 | 52.19 | 40.18 | 1.298905 | 788 | 5.098985 | | 2016-03-28 | 4496 | 49.61 | 38.34 | 1.293949 | 747 | 5.132530 | | 2016-04-01 | 5016 | 40.98 | 31.55 | 1.298891 | 520 | 6.067308 | | 2016-04-23 | 5582 | 51.60 | 39.12 | 1.319018 | 566 | 6.911661 | | 2016-05-20 | 6111 | 48.46 | 35.66 | 1.358946 | 529 | 6.741021 | | 2016-05-24 | 6450 | 25.81 | 18.65 | 1.383914 | 339 | 5.501475 | | 2016-06-07 | 6659 | 17.62 | 12.66 | 1.391785 | 209 | 6.057416 | | 2016-06-09 | 7033 | 38.91 | 27.23 | 1.428939 | 374 | 7.280749 | | 2016-06-24 | 7454 | 32.03 | 23.23 | 1.378820 | 421 | 5.517815 | | 2016-06-29 | 7827 | 28.31 | 20.38 | 1.389107 | 373 | 5.463807 | | 2016-07-08 | 8334 | 38.96 | 28.67 | 1.358912 | 507 | 5.654832 | | 2016-07-09 | 8815 | 36.65 | 27.37 | 1.339057 | 481 | 5.690229 | | 2016-07-13 | 9204 | 31.61 | 22.76 | 1.388840 | 389 | 5.850900 | | 2016-07-20 | 9667 | 34.97 | 26.51 | 1.319125 | 463 | 5.725702 | | 2016-08-22 | 10309 | 52.03 | 39.45 | 1.318885 | 642 | 6.144860 | | 2016-08-29 | 10590 | 20.70 | 15.69 | 1.319312 | 281 | 5.583630 | | 2016-09-23 | 11111 | 55.01 | 40.18 | 1.369089 | 521 | 7.712092 | | 2016-10-12 | 11712 | 56.50 | 40.10 | 1.408978 | 601 | 6.672213 | | 2016-10-30 | 12342 | 52.27 | 37.10 | 1.408895 | 630 | 5.888889 | | 2016-11-04 | 12901 | 42.20 | 30.60 | 1.379085 | 559 | 5.474061 | | 2016-11-12 | 13240 | 26.79 | 20.16 | 1.328869 | 339 | 5.946903 | | 2016-12-05 | 13865 | 54.70 | 39.67 | 1.378876 | 625 | 6.347200 | | 2016-12-27 | 14569 | 57.43 | 41.05 | 1.399026 | 704 | 5.830966 | | 2017-01-13 | 15150 | 54.47 | 38.12 | 1.428909 | 581 | 6.561102 | | 2017-01-23 | 15789 | 56.51 | 39.27 | 1.439012 | 639 | 6.145540 | | 2017-01-27 | 16040 | 23.14 | 16.54 | 1.399033 | 251 | 6.589641 | | 2017-02-25 | 16585 | 56.14 | 37.70 | 1.489125 | 545 | 6.917431 | | 2017-03-23 | 17175 | 55.44 | 38.26 | 1.449033 | 590 | 6.484746 | | 2017-04-06 | 17741 | 48.18 | 32.36 | 1.488875 | 566 | 5.717314 | | 2017-04-29 | 18410 | 56.25 | 38.82 | 1.448995 | 669 | 5.802691 | | 2017-05-09 | 18702 | 26.38 | 18.59 | 1.419042 | 292 | 6.366438 | | 2017-05-28 | 19204 | 49.00 | 32.47 | 1.509085 | 502 | 6.468127 | | 2017-06-21 | 19788 | 48.03 | 33.38 | 1.438886 | 584 | 5.715753 | | 2017-06-27 | 20579 | 62.37 | 43.34 | 1.439086 | 791 | 5.479140 | | 2017-06-29 | 21229 | 52.60 | 34.63 | 1.518914 | 650 | 5.327692 | | 2017-07-01 | 21703 | 35.34 | 24.39 | 1.448954 | 474 | 5.145570 | | 2017-07-03 | 22117 | 31.23 | 21.55 | 1.449188 | 414 | 5.205314 | | 2017-07-09 | 22729 | 48.92 | 33.76 | 1.449052 | 612 | 5.516340 | | 2017-07-11 | 23196 | 37.85 | 26.49 | 1.428841 | 467 | 5.672377 | | 2017-07-14 | 23894 | 55.20 | 37.32 | 1.479100 | 698 | 5.346705 | | 2017-07-21 | 24638 | 52.70 | 37.27 | 1.414006 | 744 | 5.009409 | | 2017-08-01 | 25306 | 56.59 | 41.04 | 1.378899 | 668 | 6.143713 | | 2017-08-13 | 25885 | 50.12 | 36.08 | 1.389135 | 579 | 6.231434 | | 2017-08-19 | 26534 | 57.12 | 37.85 | 1.509115 | 649 | 5.832049 | | 2017-09-01 | 27194 | 53.74 | 38.47 | 1.396933 | 660 | 5.828788 | | 2017-09-02 | 27683 | 39.40 | 26.82 | 1.469053 | 489 | 5.484663 | | 2017-09-02 | 27978 | 25.53 | 17.50 | 1.458857 | 295 | 5.932203 | | 2017-09-06 | 28303 | 31.30 | 21.02 | 1.489058 | 325 | 6.467692 | | 2017-09-06 | 28568 | 18.79 | 12.88 | 1.458851 | 265 | 4.860377 | | 2017-09-10 | 28805 | 22.02 | 14.99 | 1.468979 | 237 | 6.324895 | | 2017-09-13 | 29154 | 31.19 | 20.81 | 1.498799 | 349 | 5.962751 | | 2017-09-13 | 29732 | 46.99 | 31.35 | 1.498884 | 578 | 5.423875 | | 2017-09-15 | 30055 | 28.30 | 18.88 | 1.498941 | 323 | 5.845201 | | 2017-09-16 | 30773 | 62.24 | 41.52 | 1.499037 | 718 | 5.782730 | | 2017-09-24 | 31356 | 50.90 | 34.23 | 1.487000 | 583 | 5.871355 | | 2017-09-30 | 31685 | 29.32 | 21.42 | 1.368814 | 329 | 6.510638 | | 2017-10-08 | 32172 | 41.74 | 29.21 | 1.428963 | 487 | 5.997947 | | 2017-10-21 | 32759 | 55.75 | 40.87 | 1.364081 | 587 | 6.962521 | | 2017-11-07 | 33358 | 54.71 | 38.83 | 1.408962 | 599 | 6.482471 | | 2017-12-11 | 33988 | 58.10 | 39.28 | 1.479124 | 630 | 6.234921 | | 2017-12-25 | 34384 | 36.20 | 26.06 | 1.389102 | 396 | 6.580808 | | 2018-01-23 | 34916 | 47.68 | 33.96 | 1.404005 | 532 | 6.383459 | | 2018-02-21 | 35259 | 43.05 | 30.23 | 1.424082 | 343 | 8.813411 | | 2018-03-30 | 35679 | 44.93 | 31.89 | 1.408906 | 420 | 7.592857 | | 2018-04-14 | 36300 | 51.80 | 36.12 | 1.434109 | 621 | 5.816425 | | 2018-04-27 | 36848 | 45.63 | 31.93 | 1.429064 | 548 | 5.826642 | | 2018-05-02 | 37539 | 64.67 | 42.02 | 1.539029 | 691 | 6.081042 | | 2018-05-06 | 38234 | 54.77 | 37.03 | 1.479071 | 695 | 5.328058 | | 2018-06-01 | 38873 | 63.24 | 39.55 | 1.598989 | 639 | 6.189358 | | 2018-06-04 | 39384 | 43.84 | 28.67 | 1.529125 | 511 | 5.610568 | | 2018-06-06 | 40015 | 63.97 | 40.51 | 1.579116 | 631 | 6.419968 | | 2018-06-09 | 40524 | 45.93 | 29.65 | 1.549073 | 509 | 5.825147 | | 2018-06-15 | 41122 | 50.51 | 32.19 | 1.569121 | 598 | 5.382943 | | 2018-06-16 | 41591 | 39.46 | 26.15 | 1.508987 | 469 | 5.575693 | | 2018-06-23 | 42186 | 52.46 | 34.42 | 1.524114 | 595 | 5.784874 | | 2018-06-29 | 42533 | 28.98 | 19.73 | 1.468829 | 347 | 5.685879 | | 2018-07-12 | 43115 | 52.09 | 34.29 | 1.519102 | 582 | 5.891753 | | 2018-07-17 | 43451 | 30.39 | 20.34 | 1.494100 | 336 | 6.053571 | | 2018-07-26 | 43949 | 53.75 | 34.26 | 1.568885 | 498 | 6.879518 | | 2018-09-14 | 44645 | 67.51 | 41.96 | 1.608913 | 696 | 6.028736 | | 2018-10-17 | 45176 | 52.41 | 35.32 | 1.483862 | 531 | 6.651601 | | 2018-11-04 | 45639 | 44.00 | 29.75 | 1.478992 | 463 | 6.425486 | | 2018-11-17 | 46153 | 46.56 | 30.45 | 1.529064 | 514 | 5.924125 | | 2018-11-25 | 46475 | 29.84 | 20.88 | 1.429119 | 322 | 6.484472 | +------------+-------+-------+-------+----------+---------+----------+