I have a database table fuel which has a fID field (primary, autoincrement), vID (for vehicle ID), volume (for amount of fuel in litres), price (for the cost of fuel per litre), meter (for the current odometer of the vehicle on the date of filling), date (the date the vehicle is being filled up), vendorID (for the fuel supplier or just where the vehicle was filled up), notes (any additional notes on the vehicle or fuel) and type (the fuel grade).
I want to make a query that would calculate the cost of fuel per each fill by multiplying the volume (litres bought) by price (cost per litre) then adding the total for all fuel entries for a particular vehicle, then lastly adding the cost of all the fuel entries in the table (the fID is unique but the vID gets repeated (as a vehicle will be filled up now and again).
This is what I'm running now but it only returns the cost of each fill up (not adding up the other fill ups).
Select
date_format(f1.date, '%y-%m-%y %H:%i:%s) as date,
f1.meter as mileage,
case when f2.meter is null then 0
else f1.meter - f2.meter
end as distance
f1.fID,
f1.volume,
f1.volume * f1.price as cost
from
fuel f1
left outer join
fuel f2
On
f2.date = (
select
max(date)
from
fuel
where
fuel.vID = f1.vID
and
fuel.date < f1.date
)
where
f1.ID = ?
Order by f1.date
Any help will be highly appreciated.
You only need to a minor change to your query, remove the WHERE clause and add a GROUP BY:
SELECT f1.vid,
DATE_FORMAT(f1.date, '%y-%m-%d %H:%i:%s') AS date,
f1.meter AS mileage,
CASE WHEN f2.meter IS NULL THEN 0
ELSE f1.meter - f2.meter
END AS distance,
f1.volume,
SUM(f1.volume * f1.price) AS cost
FROM fuel f1
LEFT JOIN fuel f2
ON f2.date = (SELECT MAX(date)
FROM fuel
WHERE fuel.vID = f1.vID AND fuel.date < f1.date)
GROUP BY f1.vid, f1.date WITH ROLLUP
For the sample data from a previous question of yours, this gives the following result. You can detect summary rows for each vehicle by null in the date column, and the overall summary row by null in the vid column.
vid date mileage distance volume cost
26 18-05-27 05:57:00 4500 0 25 18750
26 18-05-27 05:58:00 6000 1500 20 15000
26 (null) 6000 1500 20 33750
27 18-05-27 04:58:00 1200 0 15 18000
27 18-05-27 05:50:00 2000 800 5 5000
27 (null) 2000 800 5 23000
28 18-05-27 05:53:00 5000 0 15 12000
28 (null) 5000 0 15 12000
(null) (null) 5000 0 15 68750
If you're not interested in the seeing the individual entries for each vehicle, you can remove the date from the GROUP BY (and date-specific variables from the select). This query will also sum the distance and volume:
SELECT f1.vid,
SUM(CASE WHEN f2.meter IS NULL THEN 0
ELSE f1.meter - f2.meter
END) AS distance,
SUM(f1.volume) as volume,
SUM(f1.volume * f1.price) AS cost
FROM fuel f1
LEFT JOIN fuel f2
ON f2.date = (SELECT MAX(date)
FROM fuel
WHERE fuel.vID = f1.vID AND fuel.date < f1.date)
GROUP BY f1.vid WITH ROLLUP
Output (the summary row has null for vid):
vid distance volume cost
26 1500 45 33750
27 800 20 23000
28 0 15 12000
(null) 2300 80 68750