1. ## 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
PHP Code:
``` //STARTforeach(\$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 mLEFT 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_idGROUP    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.

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

3. Originally Posted by r937
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
Code:
```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.

Code:
```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.

4. 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