CODEWITHSUNDEEP
mysql
Aleksa
Aleksa

Reputation: 99

How to sum last two rows with same ID for one column and then sum again

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. I would be very grateful for help.

Upvotes: 0

Views: 453

Answers (1)

cyadvert
cyadvert

Reputation: 875

Try this. Should return carID, max LI of the two latest records and difference of KM for the two records.

SELECT c.CARID, c.lastLI AS maxLI, 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

And now you can use maxLI and diff data to insert rows

Three subqueries.... Not the best solution. But if you insist on proceedure.....

Upvotes: 1

Related Questions