Calculating a Moving Average MySQL?

Good Day,

I am using the following code to calculate the 9 Day Moving average.

SELECT SUM(close)
FROM tbl
WHERE date <= '2002-07-05'
AND name_id = 2
ORDER BY date DESC
LIMIT 9

But it does not work because it first calculates all of the returned fields before the limit is called. In other words it will calculate all the closes before or equal to that date, and not just the last 9.

So I need to calculate the SUM from the returned select, rather than calculate it straight.

IE. Select the SUM from the SELECT…

Now how would I go about doing this and is it very costly or is there a better way?

Answer

Use something like

SELECT 
  sum(close) as sum,
  avg(close) as average
FROM (
    SELECT 
      (close)
    FROM 
      tbl
    WHERE 
      date <= '2002-07-05'
      AND name_id = 2
    ORDER BY 
      date DESC
    LIMIT 9 ) temp

The inner query returns all filtered rows in desc order, and then you avg, sum up those rows returned.

The reason why the query given by you doesn’t work is due to the fact that the sum is calculated first and the LIMIT clause is applied after the sum has already been calculated, giving you the sum of all the rows present