0

I'm trying to make average consumption of fuel in my db. But I do not know how to do it.
This is an little explanation:
I have 2 tables:

Tabe CONSUM:
ID   CARID   LI        KM          DATETIME
------------------------------------------------
6     9     70.17   174857   2015-02-10 10:58:51
5     5     51.00   154785   2015-02-09 19:11:19
4     8     99.44   485627   2015-02-09 18:45:48
3     9     47.78   174114   2015-02-09 17:21:32
2     8     24.74   484175   2015-02-07 12:28:37
1     5     89.65   154201   2015-02-02 22:01:14
...

Table CARS
CARID  avglasttwo 
-----------------
5        8.73
8        6.84
9        10.58  
...

*Data as an example.

I need to make the sum of last 2 KM rows in table COSUM with same CAREID something like this (exemple for CARID 9):
km 174857 - km 174114 = 743 and then to use last inserted LI 70.17 (for CARID 9), after that sum 70.17 / (743 / 100) and insert it in table CARS avglasttwo with card ID 9. And to do that for all CARIDs.
Also after each new input it is necessary to refresh avglasttwo.

I currently do this manually and takes me a lot of time.

Edited by pritaeas: Moved to PHP

3
Contributors
3
Replies
50
Views
2 Years
Discussion Span
Last Post by kalxas
0

I would like to make php function which would work as I gave an explanation.
After insert in table CONSUM update column avglasttwo from table CARS with same carid. OR even better solution to add new column avglasttwo in table CONSUM and after insert in table CONSUM make sum and insert as avglasttwo in same table/row.

This is how it can be done with sql:

SELECT c.CARID, MAX(c.lastLI) / (MAX(c.KM)-MIN(c.KM)) AS diff
FROM (
    SELECT CONSUM.*, lastTwo.lastLI
    FROM CONSUM
    JOIN 
        (SELECT CARID, 
        SUBSTRING_INDEX(GROUP_CONCAT(LI ORDER BY ID DESC), ',', 1) AS lastLI,
        SUBSTRING_INDEX(GROUP_CONCAT(ID ORDER BY ID DESC), ',', 2) AS twoLatest 
        FROM CONSUM 
        GROUP BY CARID) lastTwo ON CONSUM.CARID=lastTwo.CARID 
            AND FIND_IN_SET(CONSUM.ID, lastTwo.twoLatest)>0) c
GROUP BY c.CARID
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.