Solved

# Calculating the Average Fuel Consumption (AFC)

Posted on 2011-10-28
466 Views
I have the following table and data:

dat=date of tanking
NoOfLiters=number of liters when tanking
mileage=number of kilometers on car

id | idvehicle | dat | NoOfLiters | mileage | AFC
-------------------------------------------------------
20 | 2 | 2.9.2011 0:00 | 62.13 | 235808 | NULL
42 | 2 | 7.9.2011 0:00 | 60.74 | 236295 | NULL
28 | 2 | 16.9.2011 0:00 | 61.51 | 237330 | NULL
27 | 2 | 16.9.2011 0:00 | 57.68 | 237876 | NULL
30 | 2 | 17.9.2011 0:00 | 27.45 | 238116 | NULL
31 | 2 | 23.9.2011 0:00 | 60.61 | 238589 | NULL
33 | 2 | 24.9.2011 0:00 | 54.75 | 239057 | NULL
36 | 2 | 30.9.2011 0:00 | 66.55 | 239621 | NULL
40 | 2 | 5.10.2011 0:00 | 53.24 | 240075 | NULL
41 | 2 | 7.10.2011 0:00 | 63.55 | 240633 | NULL
45 | 2 | 15.10.2011 0:00 | 87.52 | 241027 | NULL
43 | 7 | 7.10.2011 0:00 | 66.2 | 179770 | NULL
47 | 7 | 17.10.2011 0:00 | 94.65 | 180603 | NULL
37 | 12 | 1.10.2011 0:00 | 40.5 | 226801 | NULL
51 | 12 | 18.10.2011 0:00 | 62.98 | 228032 | NULL
53 | 12 | 21.10.2011 0:00 | 62.56 | 228565 | NULL
22 | 13 | 7.9.2011 0:00 | 78.91 | 102433 | NULL
24 | 13 | 8.9.2011 0:00 | 21.7 | 102625 | NULL
29 | 13 | 16.9.2011 0:00 | 89.83 | 104244 | NULL
39 | 13 | 3.10.2011 0:00 | 88.47 | 105155 | NULL
48 | 13 | 17.10.2011 0:00 | 94.03 | 106040 | NULL
21 | 14 | 7.9.2011 0:00 | 64.81 | 121027 | NULL
23 | 14 | 8.9.2011 0:00 | 26.41 | 121184 | NULL
26 | 14 | 14.9.2011 0:00 | 86.43 | 122649 | NULL
35 | 14 | 30.9.2011 0:00 | 95.27 | 124250 | NULL
55 | 14 | 7.10.2011 0:00 | 89.47 | 124958 | NULL
56 | 14 | 21.10.2011 0:00 | 87.33 | 125570 | NULL
57 | 14 | 24.10.2011 0:00 | 68 | 126083 | NULL
25 | 17 | 13.9.2011 0:00 | 84.98 | 37553 | NULL
38 | 17 | 1.10.2011 0:00 | 64.41 | 38141 | NULL
52 | 17 | 19.10.2011 0:00 | 31.52 | 39517 | NULL
32 | 19 | 23.9.2011 0:00 | 51.92 | 93704 | NULL
34 | 19 | 30.9.2011 0:00 | 47.12 | 94305 | NULL
44 | 19 | 10.10.2011 0:00 | 41.35 | 95387 | NULL
46 | 19 | 17.10.2011 0:00 | 50.57 | 95946 | NULL
54 | 19 | 20.10.2011 0:00 | 49.04 | 96556 | NULL

MY QUESTION: How to calculate the Average Fuel Consumption (AFC) using SELECT query for each vehicle and every row of AFC column in the table above?

-first AFC of each vehicle cannot be calculated due to lack of data, that's why the result of it can be 0.0
-each row in AFC column has to have a numeric value (type DOUBLE), and should be calculated like that (example shows calculating AFC for row with ID 42 only):
* mileage of row with id 42 (236295) MINUS mileage of row with id 20 (235808) is 487
* NoOfLiters of row with id 42 (60.74) MULTIPLIED BY 100 is 6074
* 6074 DEVIDE with 487
* result for AFC of row with id 42 is 12.47227926078029
0
Question by:JanjaNovak

LVL 41

Accepted Solution

something like this?

``````select
a.*,
ifnull(a.NoOfLiters * 100.00 / (a.mileage - (select mileage from yourtable where idvehicle = a.idvehicle and dat < a.dat order by dat desc limit 1)), 0) as AFC
from yourtable a
``````
0

LVL 50

Expert Comment

which database system are you dealing with (version and edition, please)

in generic sql like this

select A.*
,case when b.idvehicle is null then 0.00
else (a.noofliters * 100.00)/(a.mileage - b.mileage)

end as AFC
from yourtable as A
left outer join yourtable as B
on a.idvehicle=b.idvehicle
and a.dat>b.dat
where ( not exists (select idvehicle
from yourtable as x
where a.idvehicle=x.idvehicle
and x.dat between b.dat and a.dat)
)
or b.idvehicle is null
order by a.idvehicle,a.dat
?
0

## Featured Post

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…