Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • 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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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