avg() returns odd result 1

[Microbe]

Hey folks,

I have never used avg() before and am puzzled by the result I am getting.

SQL code

SELECT * FROM s12345 ORDER BY id DESC LIMIT 5

plus php code

do{
  echo $row_trend["id"] . " = " . $row_trend["height"]. "<br>";
 }while($row_trend = mysql_fetch_assoc($trend));

gives me the expected rows as follows

68 = 0.30
67 = 0.30
66 = 0.30
65 = 0.30
64 = 0.32

However, the SQL

SELECT avg(height) as avg FROM s12345 ORDER BY id DESC LIMIT 5

plus PHP

echo "<div>The average of the 5 readings is " . $row_trend["avg"];

gives me

"The average of the 5 readings is 0.332941"

The actual average is .304 What am I doing wrong?

Steve

http://www.ColdCallComedy.com

- I have fun with telemarketers

 

[tsuji]

Make a derived table (alias drtble, say) then average over it. Like this.
[tt]
SELECT avg(drtbl.height) as avg FROM (SELECT height FROM s12345 ORDER BY id DESC LIMIT 5) AS drtbl;
[/tt]
(I qualify there drtble.height with alias drtble for clarity, it works too without.)

 

[r937]

microbe, the AVG() function is processed on the entire table before the LIMIT is applied to the results -- thus, there is only one row being returned

note that some versions of mysql won't let you do LIMIT in a subquery

if you're dealing with only 5 rows, i would simply do the average in php

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Microbe]

Hah! The whole table, not the limit.

Thanks for that.

Is that in the manual? Who RTFM anyway )

Steve

http://www.ColdCallComedy.com

- I have fun with telemarketers

 

[tsuji]

I wonder if members could run the sql I posted on different mysql versions they possess to see how it would or not be working (err message). It certain works on mine.

Whether avg() is evaluated before the "from subquery", I would only deduce something from the documentation on "Restriction on Subqueries" (D.3) on a different sort of "from subqueries".
quote
... Subqueries in the FROM clause cannot be correlated subqueries. They are materialized (executed to produce a result set) before evaluating the outer query, ...
unquote
So I might deduce without acquiring the support of source code that avg() is evaluated _after_ the derived table is made and the LIMIT consummed. I could be wrong.