Sql query to calculate average of each user

Databases
1

I’m trying to make a diesel consumption scoreboard for the drivers in our company, but not sure how I can sort the drivers by average fuel consumption. I have two database tables, one for the members and one for the fuel data.

I’m using this sql query to calculate the average, but I would like to have some more information in the same query and also a way to easily sort the members by their average fuel consumption to complete the scoreboard.

[COLOR=#282828][FONT=helvetica]SELECT [COLOR=#800000]10[/COLOR] * SUM(liter) / (MAX(km) - MIN(km)) [COLOR=#00008B]as[/COLOR] [COLOR=#2B91AF]AvgFuel [/COLOR]FROM diesel WHERE diesel.dato >= [COLOR=#800000]'$dx'[/COLOR] AND userid = [COLOR=#800000]".$_COOKIE['userid']."[/COLOR];[/FONT][/COLOR]

In the diesel table there are information like km, liters, dates, locations etc.

How can I extend this query to have more information instead of having multiple queries ? (I’m a newbie)

Thanks for reading!

2

the query you posted looks like it’s designed for just one specific user, and doesn’t actually return anything from the user table

so to produce a result of averages for all users, you need a join –

SELECT u.userid
     , 10 * SUM(d.liter) / (MAX(d.km) - MIN(d.km)) as AvgFuel
  FROM users AS u
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = u.userid
   AND d.dato >= '$dx'
GROUP
    BY u.userid

i guessed the users table and userid column names

you can also add other user columns to the SELECT list

3

Thank you. The query I had were used on a “my info” page, but I’m testing your query now on a test page, but I don’t get any results or errors at all. I guess its something I’m missing here. Here is the code I’ve got


include 'connection.php';


$dx = date('Y-m-d', strtotime('today - 30 days'));

$result = mysqli_query($con,"SELECT m.id, 10 * SUM(d.liter) / (MAX(d.km) - MIN(d.km)) as AvgFuel 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   AND d.dato >= '$dx' 
GROUP
    BY m.id");
    
while($row = mysqli_fetch_array($result)){ 
    $averagefuel = $row['AvgFuel']; 
    $membername = $row['username']; 


} 


echo $membername."<br />";

The member table in the database contains just about the name and username of each member. The diesel table has one row for each time they fill up their trucks with dates, liters, km, location, etc…

Thanks :slight_smile:

4

Sorry. My bad. I got it working this way


include 'connection.php';
$dx = date('Y-m-d', strtotime('today - 30 days'));
$result = mysqli_query($con,"SELECT m.id, 10 * SUM(d.liter) / (MAX(d.km) - MIN(d.km)) as AvgFuel   FROM members AS mLEFT OUTER  JOIN diesel AS d    ON d.userid = m.id   AND d.dato >= '$dx' GROUP    BY m.id");    while($row = mysqli_fetch_array($result)){     echo $row['AvgFuel']."<br />"; 
}

but how can I order this query by average desc. ?

5

oh yeah, i forgot that part

SELECT m.id
     , 10 * SUM(d.liter) / (MAX(d.km) - MIN(d.km)) as AvgFuel 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   AND d.dato >= '$dx' 
GROUP
    BY m.id
ORDER
    BY AvgFuel DESC

you will also want to echo $row[‘id’]

6

Thanks for you help. This is great, now I can have the highscore working :slight_smile: