I have a 10 million+ row table that looks like this:
id | time_read | value
-----------------------
9 1111111111 100
9 1111111222 200
9 1111111333 150
9 1111111444 225
I want to AVG the value of every 4 rows. How do I do this in MySQL?
Also, secondary to my question, how might I do this if I wanted to avg the value of every 4 rows for all the data in a month and return that total for each month of each year in the table? For example, using the same table in my first question, ending up with:
2008 | 12 | 500000
2009 | 01 | 450000
2009 | 02 | 475000
edit: In other words, the total of each average 4 rows grouped by year-month. Each time_read is 15 minutes apart.
I was doing something like this previously, but it wasn't accurate enough. I need to average every 4 rows and total that instead of taking the sum of all value's in a month and dividing by 4.
SELECT DATE_FORMAT(FROM_UNIXTIME(time_read),'%Y %m') as tr,
DATE_FORMAT(FROM_UNIXTIME(time_read),'%Y') as year,
DATE_FORMAT(FROM_UNIXTIME(time_read),'%m') as month, SUM(value) as value
FROM table WHERE id = 9
GROUP BY tr