CODEWITHSUNDEEP
mysqlsql
KiloVoltaire
KiloVoltaire

Reputation: 275

Calculate Gas Mileage

I'd like to track my gas mileage ("MPG") from tank to tank.

Here is my Stops table:

stopID, int(11) auto_increment
vendorID, int(11)
vehicleID, int(11)
mileage, int(11)
date, date
gallons, float
cost, float
remarks, varchar(32)

Here is my Vendors table: 

vendorID, int(11) auto_increment
vendor, varchar(32)
street, varchar(32)
city, varchar(32)
state, varchar(2)
zip, varchar(5)

Here is my Vehicles table:

vehicleID, int(11) auto_increment
year, year(4)
make, varchar(16)
model, varchar(16)
type, varchar(16)
vin, varchar(32)

So far I'm using the following query (it works), to which I'd like to add a calculated MPG column:

SELECT v.vendorID, 
       v.vendor,  
       v.city,  
       v.state,  
       FORMAT(s.mileage , 0 ) Miles ,  
       DATE_FORMAT(s.date , '%m/%d/%y' )  Date ,  
       FORMAT(s.gallons , 3 ) Gal ,  
       FORMAT(s.cost , 2 ) Cost ,  
       FORMAT(s.cost /s.gallons , 3 ) Rate 
FROM Stops s 
INNER JOIN Vendors v  
       ON s.vendorID =v.vendorID  
       AND s.remarks ='Fuel'  
       AND s.vehicleID =2  
       AND s.date >= '2014-07-01'
ORDER BY `Date`  DESC

Here is a typical output from the existing query (without MPG column):

vendorID    vendor  city        state   Miles   Date        Gal     Cost    Rate    (MPG)
14          Sunoco  Arnold      MD      122,104 07/18/14    3.741   15.22   4.068
14          Sunoco  Arnold      MD      121,936 07/16/14    3.633   14.89   4.099
14          Sunoco  Arnold      MD      121,770 07/14/14    3.276   13.43   4.100
29          BP      Washington  DC      121,620 07/11/14    3.635   15.26   4.198

I tried looking for answers already (both Stack Overflow and in the mySQL documentation), but they all seem to depend on having consecutive ID values, or having a particular column sorted in a certain way. Since I have multiple vehicles and multiple stops (not just for remarks = 'Fuel'), those solutions don't work. Please feel free to ask any questions to clarify, and I'll update my question as needed to provide additional info.

Upvotes: 0

Views: 1750

Answers (2)

aa333
aa333

Reputation: 2576

Following a suggestion I made in the question comments, if you add a self reference column to Stops the solution would be a lot easier without the need for maintaining any order:

Stops table would be:

stopID, int(11) auto_increment vendorID, int(11) vehicleID, int(11) mileage, int(11) date, date gallons, float cost, float remarks, varchar(32) previous_stopID int(11)

And the query to get MPG would be:

SELECT cur.stopID, cur.vendorID, (cur.mileage-prev.mileage)/cur.gallons as MPG FROM Stops cur INNER JOIN Stops prev WHERE cur.prev_StopID = prev.stopID

You could then join/merge this with Vendors to get any additional columns you need.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

You are going to have to use a self join. This answer will show you the general idea, but it's untested and may have errors.

select (ThisStop.mileage - PreviousStop.mileage) / ThisStop.gallons mpg
from stops.ThisStop join stops.PreviousStop on ThisStop.vehicleId = PreviousStop.vehicleId
where PreviousStop.date = 
(select max(date) from stops
where vehicleId = ThisStop.vehicleId
and date < ThisStop.date)

If you want to identify the vendor, get it from PreviousStop.

Upvotes: 0

Related Questions