CODEWITHSUNDEEP
mysql
Mawg
Mawg

Reputation: 40215

How to get the average of the last X values?

If I have a table with two columns - value and time_stamp - how can I use SQL to get the average of the last X values?

One statement or several, it doesn't matter.

Maybe something like SELECT value FROM table ORDER BY time_stamp DESC LIMIT x ? Then in my program, I could get each value, total them and average them? That's my best guess so far as I am a SQL beginner.

I am aware that SQL can total / average, but I am unsure how to combine that with a request to apply it to the latest X values...

Note to anyone else seeking to do this: in addition to the accepted answer for the last X values, I also found by googling that it is easy to do for a time period, such as a minute or hour, by using something like SELECT AVG(somefield) FROM sometable WHERE datefield BETWEEN '2003-08-10' AND '2003-08-26'.

Upvotes: 2

Views: 3634

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270795

You use the AVG() aggregate on the result of a subselect which does the initial LIMIT. Just be sure to substitute the correct LIMIT value for n on the inner query.

SELECT AVG(a.value) AS the_average
FROM (SELECT value FROM tbl ORDER BY time_stamp DESC LIMIT n) a;

Upvotes: 6

Related Questions