Special AVG() query sought

[joeNmass]

Hi everyone,

 

I hope someone can give me an answer to problem that has eluded me for about 2 weeks. I just can not find the answer.

 

My problem is that I have a database that I want to return the average of a column BUT the average number that gets calculated can not include the oldest two records that were entered in that particular column.

 

In other words, if I have ten entries in a column only the latest 8 entries will be used to calculate the returned average.

 

Here is the basic query I am using:

 

$result = mysql_query("SELECT *, avg(miles) AS av_miles FROM mpg WHERE vehicle_id='$id' ");

 

Thank you for help in advance.

 

[Psycho]

You'll want to use two queries. It might be able to be done in one, but I think it would get overly complicated.

 

Query #1 will be to get the number of records that match the condition

SELECT COUNT(miles) as limit_amt
FROM mpg
WHERE vehicle_id='$id'

 

Then take the value from that query, subtract 2, and assign it to a variable (let's say $limit_amt).

 

Now run this query to get the average of the records except the oldest two

SELECT AVG(miles) AS av_miles
FROM (SELECT miles
      FROM mpg
      WHERE vehicle_id='$id'
      ORDER BY date DESC
      LIMIT $limit_amt) as data_set

[joeNmass]

Thank you very much for your response mjdamato.

 

Please excuse my ignorance in this matter as I am also a little embarrassed for not knowing (kinda of a newbie). I have the first query working correctly as you suggested. Here it is as I have it written:

 

$q="SELECT *,  COUNT(miles) AS limit_amt FROM mpg WHERE vehicle_id='$id'";

$result = mysql_query($q) ;
while($row = mysql_fetch_array($result)){
$limit_amt=$row["limit_amt"];
$amt=($limit_amt - 2);
echo $amt;
echo '<br/>There are'; echo  $amt;  
echo 'items.';
echo "<br />";

 

The second query is where I have a problem. I don't under stand how to use the " as data_set" at the end of the query. I have never used something like that at the end of a query before.

 

This is how I have the second query written, but with no luck.

 

$q2="SELECT AVG(miles) AS av_miles
FROM (SELECT miles    FROM mpg   WHERE vehicle_id='$id'   ORDER BY date DESC   LIMIT $limit_amt) as data_set";
}

$result = mysql_query($q2) ;

// Print out result
while($row = mysql_fetch_array($result)){
$data_set=$row["data_set"];
echo $data_set;
echo '<br/>There are'; echo  $data_set; echo 'items.';
echo "<br />";  

 

Any further suggestions would be great....and thank you again.

[Psycho]

In the first query why are you using "SELECT *, COUNT(miles)"? Remove the "*," from the SELECT. Also, why are you using a while() loop onthat query result? You only want ONE record returned.

 

As for the second query - you need to reference the av_miles value from the query; you don't need to reference 'data_set' at all. However, when you create a "dynamic" table as is done with the subquery you need to give it a name.

 

$query = "SELECT (COUNT(miles) - 2) AS record_count
          FROM mpg
          WHERE vehicle_id='$id'";
$result = mysql_query($query) ;
$record_count = mysql_result($result, 0);
echo "<br/>There are {$record_count} items<br>\n";

$query = "SELECT AVG(miles) AS av_miles
          FROM (SELECT miles
                FROM mpg
                WHERE vehicle_id='$id'
                ORDER BY date DESC
                LIMIT {$record_count}) as data_set";
$result = mysql_query($query) ;
$avg_miles = mysql_result($result, 0);
echo "<br/>The average miles are {$avg_miles}<br />\n";

[joeNmass]

Hi mjdamato,

 

You are an amazing genius with this stuff.

 

That worked perfectly. I studied your code so I could understand how it relates from one line to the next and it all makes sense now (it's like being perplexed watching a magician do his amazing tricks but once he shows you how its done you say "oh, so thats how its done"....lol .

 

Thank you again and have a great weekend.

 

[Psycho]

Just be aware that you should put some validation on that code. The first query could result in -1 or -2 - which would likely result in an error when running the 2nd query (never tired a negative value on the LIMIT clause). Or, if the result of the first query is 0, you would get 0 for the second query (even though there were records for the vehicle_id, but only 2).

 

So says the amazing Kalamazan!