SQL AVG() function returning incorrect values

Question

I want to use the AVG function in sql to return a working average for some values (ie based on the last week not an overall average). I have two values I am calculating, weight and restingHR (heart rate). I have the following sql statements for each:

SELECT AVG( weight ) AS average 
FROM   stats 
WHERE  userid='$userid' 
ORDER BY date DESC LIMIT 7

SELECT AVG( restingHR ) AS average 
FROM   stats 
WHERE  userid='$userid' 
ORDER BY date DESC LIMIT 7

The value I get for weight is 82.56 but it should be 83.35 This is not a massive error and I'm rounding it when I use it so its not too big a deal.

However for restingHR I get 45.96 when it should be 57.57 which is a massive difference.

I don't understand why this is going so wrong. Any help is much appreciated.

Thanks

Answer 1

Use a subquery to separate selecting the rows from computing the average:

SELECT AVG(weight) average
FROM (SELECT weight
      FROM stats
      WHERE userid = '$userid'
      ORDER BY date DESC
      LIMIT 7) subq

Answer 2

It seems you want to filter your data with ORDER BY date DESC LIMIT 7, but you have to consider, that the ORDER BY clause takes effect after everything else is done. So your AVG() function considers all values of restingHR from your $userId, not just the 7 latest.

To overcome this...okay, Barmar just posted a query.