• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

Calculating the Average Fuel Consumption (AFC)

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?

Also please consider:
-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
JanjaNovak
Asked:
JanjaNovak
1 Solution
 
ralmadaCommented:
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

Open in new window

0
 
LowfatspreadCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now