CODEWITHSUNDEEP
mysqlsqlaverage
Chiyaan Suraj
Chiyaan Suraj

Reputation: 1015

SQL- How to find average of a column using values only in last N rows

I have a data table which looks like this.

datestamp   timestamp   vix    
2015-05-13   16:30:00   18.0     
2015-05-14   16:30:00   19.2
2015-05-15   16:30:00   18.5
2015-05-18   16:30:00   17.5
2015-05-19   16:30:00   17.0

I want to get the average(avg()) of the column vix for last 3 days. I know how to get last 3 rows using query like this 

select vix from my_table order by date desc limit 3

How can i further compute average of last 3 days? Any help would be appreciated.

Upvotes: 1

Views: 3788

Answers (2)

Arth
Arth

Reputation: 13110

This depends if you want the last 3 days or the last 3 records..

For strictly the last three days:

SELECT AVG(t.vix)
  FROM table t
 WHERE t.datestamp > CURDATE() - INTERVAL 3 DAY

is preferable, otherwise go for Jarlh's answer

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Use a derived table:

select avg(dt.vix)
from
(select vix from my_table order by date desc limit 3) dt

Upvotes: 5

Related Questions