Complicated query question

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!

any chance we could tackle your queries one at a time, and without the php code?

Sure, I’m sorry I pasted all the code in. I would like to make these two queries into one if possible. I’m a newbie and don’t know much about either php or sql queries.

Query1 : Get the first fill up of the tank in June 2014

SELECT d.vehicle_id, d.liter FROM diesel AS d LEFT OUTER JOIN members AS m 
            ON m.id = d.userid WHERE YEAR(dato) = 2014 AND MONTH(dato) = 06 AND d.liter > 0 AND m.atruck = d.vehicle_id GROUP BY d.userid

Query2: Calculate average fuel consumption for June 2014 - To make the calculation right I need to subtract the $FirstFill variable from the total amount of fuel used in June.

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) = 2014 AND MONTH(dato) = 06 AND d.liter > 0 AND m.atruck = d.vehicle_id
GROUP
    BY m.id ORDER BY AvgFuel ASC

I need to subtract the value I get in the first query with the SUM in the second.

i sort of understand the issue but it would take a lot of time, of which i am unfortunately in very short supply

i’m sure there are a couple other people here that can do it

:slight_smile: