Member Avatar for logicaweb

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 because: Moved to PHP
  • 3 Contributors
  • 3 Replies
  • 241 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by kalxas
Member Avatar for pritaeas

Can you provide a small SQL dump to test with? Or put it on SQLFiddle.

Edited by pritaeas
Member Avatar for logicaweb

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
Member Avatar for kalxas

try to post in the php section

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.