I’m trying to make an sql query to calculate the average fuel usage and make a high score list. To calculate correctly I need to do some calculations. Like this LITERS - “the first tanking” / KM * 10 = L/10km
Here is my non-working query. The problem is to find the first tanking for each user and subtract that from the total amount of liters in a given date range. Like all tankings in June - the first tanking.
Hope you understand what I mean
The query
//START
foreach($oDB->query("SELECT d.vehicle_id, d.liter FROM diesel AS d
LEFT OUTER
JOIN members AS m
ON m.id = d.userid
WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id GROUP BY d.userid") as $row) {
$FirstFill = $row['liter'];
global $FirstFill;
}
//END
$query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel
FROM members AS m
LEFT OUTER
JOIN diesel AS d
ON d.userid = m.id
WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 AND m.atruck = d.vehicle_id
GROUP
BY m.id ORDER BY AvgFuel ASC";
$sql=$oDB->prepare($query);
$sql->execute();
$row = $sql->fetchAll();
return $row;[COLOR=#282828][FONT=helvetica][/FONT][/COLOR]
This one kind of works, but the $FirstFill variable gets wrong cause it will subtract the same amount of gas on each user which is wrong. I would need some help to get this right, and possible all in one query. I was messing around with UNION without luck.
Thanks for reading!