Calculate the average consumption

Question

Calculate the average consumption

Navigation

#1 by (2 votes)

1

I have a table in a database:

consumo

pk_consumo   fk_ruta    fk_vehiculo   fecha      kms     litros    litrosx100
-----------------------------------------------------------------------------
   1           1          1      2018-04-25    345.000   80         
   2           2          2      2018-04-26    351.000   70           
   3           3          1      2018-04-27    360.000   60           x

Each vehicle makes a route and on each route can make a gasoline refueling.

I want to make an average of liters consumed in a route with a SELECT , but of the vehicle number 1 . In this case we have 3 different routes. I want to make the average consumption on route number 3, where is the x.

How could I do it? I have tried many things but I can not get it out.

The formula I have in mind is the following:

((kms de ruta 3 - kms de ruta 1)/100) / litros de la ruta 3

The result I want is the following:

pk_consumo   fk_ruta    fk_vehiculo   fecha      kms     litros    litrosx100
-----------------------------------------------------------------------------
   1           1          1      2018-04-25    345.000   80         
   2           2          2      2018-04-26    351.000   70           
   3           3          1      2018-04-27    360.000   60          4,5

I tried the following, but it does not work out:

select (((select sum(kms) from consumo where pk_consumo = 3) - (select sum(kms) from consumo where pk_consumo = (3-1)))/100)/(select sum(litros) from consumo where pk_consumo = 3) total 
from consumo 
where fk_vehiculo = 2 
group by total

mysql sql

asked by Xerox 26.04.2018 в 08:58

source

1 answer

I need Help with async Task. Print ArrayList in TextView

score 2 · Accepted Answer

This SQL query is based on obtaining all the required records of <ruta deseada> and one by one obtains the immediately previous record of the same vehicle to calculate the distance it has traveled in it and the fuel consumption:

SELECT
  co.*,
  100 * (litros_a - litros) / (kms - kms_a) litrosx100
FROM (
  SELECT
   *,
   (
     SELECT kms
     FROM consumo co2
     WHERE
       co1.fk_vehiculo = co2.fk_vehiculo AND co2.pk_consumo < co1.pk_consumo
     ORDER BY pk_consumo DESC
     LIMIT 1
   ) kms_a,
   (
     SELECT litros
     FROM consumo co2
     WHERE
       co1.fk_vehiculo = co2.fk_vehiculo AND co2.pk_consumo < co1.pk_consumo
     ORDER BY pk_consumo DESC
     LIMIT 1
   ) litros_a
  FROM consumo co1
  WHERE fk_ruta = <ruta deseada>
) co
WHERE kms_a IS NOT NULL;

The nested queries get a list of the most recent to the oldest of the trips of the same vehicle, keeping the data of litros and kms of the most recent:

SELECT litros
  FROM consumo co2
  WHERE
    co1.fk_vehiculo = co2.fk_vehiculo AND co2.pk_consumo < co1.pk_consumo
  ORDER BY pk_consumo DESC
  LIMIT 1

Once the nested query co is resolved, we discard those trips in which the consumption calculation can not be performed because they do not have a previous record with:

WHERE kms_a IS NOT NULL

And the calculation I modified it to the following to correctly calculate the consumption at 100 km:

100 * (litros_a - litros) / (kms - kms_a)

Online version: link

After the debated in the chat I changed the way I solve the problem adding support for multiple vehicles. To see the previous answer click here .