mysql and php - subtract the last two records from the same column and divide by the last record from another column

Question

mysql and php - subtract the last two records from the same column and divide by the last record from another column

Navigation

#1 by (1 votes)

2

I have a carro_log table that keeps the update history of another table.

id  kilometragem  combustivel  dt_atualização
 1      200           45         2017-05-03
 2      400           35         2017-05-03
 1      150           38         2017-05-02
 3      220           30         2017-05-01
 .       .             .             .  
 .       .             .             .

The car yield calculation is (current mileage - previous mileage) / current fuel of each id (car). How can I do this? Select and subtract the current and previous value from the same column?

php mysql

asked by anonymous 04.05.2017 / 07:33

1 answer

How to call initLoader within OnClickListener MySQL How to return only part of the result of the FULL-TEXT query

score 1 · Accepted Answer

The query below will return in a new column the yield (according to its formula):

select *,
       cast((select (car.kilometragem - cAnt.kilometragem) * 1.0
             from carro_log cAnt
             where cAnt.id = car.id 
                   and cAnt.dt_atualizacao < car.dt_atualizacao
             order by dt_atualizacao desc
             limit 1)
            / car.combustivel as decimal(7,2))  as 'Rendimento'
from carro_log car
order by dt_atualizacao

For the same table I made two selects :
- car = CURRENT
- cAnt = THE PREVIOUS REGISTRATION

In the subquery subtraction is done by the immediately preceding record (using limit 1 to guarantee only one (1)) and I also made a multiplication by 1.0 - to convert to float (could be with cast normally) . Finally, the split by the current fuel is made and the conversion to decimal(7,2) - to only two decimal places.