Query Mysql For Unique Values

Question

I have a database table for fuel with the following details

The fID is the fuel id, vID is the vehicle id, volume is the amount of fuel in litres, price is the price of fuel per litre, date is the date of filling up the vehicle, type is the type/grade of fuel.

I wanted to make a query that would return the cost of fuel by multiplying the cost of fuel per litre with the volume of fuel filled up, plus another field for distance which should be the difference between the previous fill up meter and the latest meter. In the table, there will be a lot of cars, so I just want to return records of a specific vehicle (the vID will be repeated but the fID is unique).

I have the following query so far but it returns empty if there's only one fuel entry in the database, plus I can't figure out how to calculate the cost in Mysql.

Select
t1.*, t2.meter - t1.meter as distance
From fuel t1 left join fuel t2 
on t1.date > t2.date 
where t1.vID = t2.vID 
order by t1.date desc

How should I go about it the right way?

Here is the schema:

CREATE TABLE IF NOT EXISTS `fuel` (
  `fID` int(11) NOT NULL AUTO_INCREMENT, 
  `vID` int(11) DEFAULT NULL, 
  `volume` varchar(100) DEFAULT NULL, 
  `price` varchar(100) DEFAULT NULL, 
  `meter` varchar(100) DEFAULT NULL, 
  `date` datetime DEFAULT NULL, 
  `vendorID` int(11) DEFAULT NULL, 
  `notes` text, 
  `type` varchar(50) DEFAULT NULL, 
  PRIMARY KEY (`fID`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1;

INSERT INTO `fuel` (`fID`, `vID`, `volume`, `price`, `meter`, `date`, `vendorID`, `notes`, `type`) 
VALUES 
  (7, 28, '15', '800', '5000', '2018-05-27 05:53:00', NULL, 'Entry number one for this vehicle', 'Unleaded'), 
  (6, 27, '5', '1000', '2000', '2018-05-27 05:50:00', NULL, 'This is the second fill up for this vehicle', 'Leaded'), 
  (5, 27, '15', '1200', '1200', '2018-05-27 04:58:00', NULL, 'Hey there vendors!', 'Leaded'), 
  (9, 26, '25', '750', '4500', '2018-05-27 05:57:00', NULL, NULL, 'Leaded'), 
  (10, 26, '20', '750', '6000', '2018-05-27 05:58:00', NULL, NULL, 'Leaded');

This is how I want to output the data that will be returned. This picture takes an example of getting all fuel history logs for a vehicle vID 27. The first entry should return distance 0. The second one should subtract its current meter reading from the previous record that also has a vID of 27 (here its 800)... Any ideas how I can achieve this?

Following Nick's answer, I implemented the following in PHP, but it throws an error. However, when I run it in mysql sql command it returns the results as expected...

$vID = 27;
$pdo = $this -> con -> query("
     select 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, 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) 
     where f1.vID = ? order by f1.date ");


if($pdo -> execute([$vID]) && $pdo -> rowCount()) { 
     $res = $pdo -> fetchAll(5); 
     $this -> response($this -> json($res), 200); // send fuel logs 
} else { 
   $this -> response('', 204);  // If no records "No Content" status 
}

Here is the error I get after executing the code through php.

Answer 1

OK SQL can also achieve what you wanted, but it is more intuitive through store procedure. (i will also mention that controller and view i.e. PHP and jquery could also achive what you want but much more hard that way. In jquery case; mysql just needs to return that vehcle recordset.) Here is the code

-- Proceuodo code
-- create a temporary table
-- set next_mileage = 0;
-- set pre_mileage = 0;
-- get a recordset of the desire vehicle
-- now loop through this recordset based on mileage
    -- if pre_mileage = 0 then pre_mileage = mileage(i);
    -- set next_mileage = mileage(i);
--        calculate distance (by subtracting next_mileage from previous_mileage) and other fields


DELIMITER @@
DROP PROCEDURE get_vehicle_info @@
CREATE PROCEDURE get_vehicle_info
    (IN vid INT)
BEGIN

    DECLARE v_vID BIGINT;
    DECLARE v_volume BIGINT;
    DECLARE v_price BIGINT;
    DECLARE v_meter BIGINT;
    DECLARE v_date datetime;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur1 CURSOR FOR SELECT vID, volume, price, meter, `date` FROM vehicle_records;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DROP TEMPORARY TABLE IF EXISTS vehcle_info;
    CREATE TEMPORARY TABLE vehcle_info (`Date` datetime, Mileage BIGINT, Distance BIGINT, Volume BIGINT, Cost BIGINT);
    SET @sqlStr = CONCAT('CREATE OR REPLACE view vehicle_records as SELECT vID, volume, price, meter, `date` FROM fuel WHERE vID = ', vid, ' ORDER BY meter');
    PREPARE stmt FROM @sqlStr;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt; 

    SET @pre_mileage = 0;
    SET @next_mileage = 0;

    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO v_vID, v_volume, v_price, v_meter, v_date;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF @pre_mileage = 0 THEN
            SET @pre_mileage = v_meter;
        END IF;
        SET @next_mileage = v_meter;

        INSERT INTO vehcle_info VALUES (v_date, v_meter, @next_mileage - @pre_mileage, v_volume, v_price * v_volume);
        SET @pre_mileage = v_meter;
    END LOOP;
    CLOSE cur1;
    SELECT * FROM vehcle_info;

    DROP VIEW IF EXISTS vehicle_records;
    DROP TEMPORARY TABLE vehcle_info;
END @@ 
DELIMITER ; 

CALL get_vehicle_info(27);

Answer 2

Nicks solution will work, but the given join condition is a sub-query which runs for every join. You can also try this one, for the expected result.

select f3.date, f3.meter, f3.cost, f3.volume, f3.price, min(f3.distance) as distance from (
  select  f1.date,
    f1.meter,
    case when f2.`meter` is NULL then 0
      else f1.meter - f2.meter end
      as distance,
    (f1.price * f1.volume) as cost,
    f1.`volume`,
    f1.`price`,
    f1.fID
  from fuel f1
  left join fuel f2 
  on f1.vid = f2.vid and f1.`date` > f2.date
  where f1.vid = 27
  group by f1.fID, distance
  order by date, distance asc) f3
group by f3.fID;

@Nick thanks for the feedback. Hope this query will work.

Answer 3

This query will give you the individual rows that you want. The query works by joining fuel to itself using the latest fill-up date for this vehicle prior to the current fill-up date. If there is no prior fill-up date, the CASE expression produces a 0 result for distance.

SELECT 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,
  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)
WHERE f1.vID = 27
ORDER BY f1.date

Output:

date                mileage     distance    volume  cost
18-05-27 04:58:00   1200        0           15      18000
18-05-27 05:50:00   2000        800         5       5000

Demo

If you don't want to sum the rows in PHP, the query can produce a summary row with a minor change to the query (adding an aggregation function and a GROUP BY WITH ROLLUP clause):

SELECT 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)
WHERE f1.vID = 27
GROUP BY f1.date WITH ROLLUP

Output:

date                mileage     distance    volume  cost
18-05-27 04:58:00   1200        0           15      18000
18-05-27 05:50:00   2000        800         5       5000
(null)              2000        800         5       23000

You can detect the summary row in PHP by the fact that the date column is null. Demo