计算mysql中的总成本总和

Question

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.

Answer 1

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

Demo

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

Demo