MySQL Average Every 4 Rows

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 o开发者_开发技巧f 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

If you don't want to rely on your "id" column (it might not be sequential), you can just create your own variable to group by that changes every 4 rows, like so:

SET @rank = 0;
SELECT AVG(value), CEIL((@rank:=@rank+1)/4) AS rank FROM "your_table" GROUP BY rank;

I don't know if you did this on purpose, but I think it's just a little typo: you're ID is 9 for every row.
If it is, you may be looking for this query:

SELECT AVG(value) AS val, CEIL(id / 4) AS group_id FROM table GROUP BY group_id

2nd question:

SELECT SUM(val) FROM (SELECT AVG(value) AS val, CEIL(id / 4) AS group_id FROM table GROUP BY group_id) AS fred

At this time I cannot provide a where-part for the second question, since I don't know how you're filtering things by date (I don't see a date-column or something). So for now, this will calculate the sum of all average values grouped by 4.

Try this code -

CREATE TABLE table1 (
  id INT(11) NOT NULL AUTO_INCREMENT,
  time_read INT(11) DEFAULT NULL,
  value INT(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO table1 VALUES 
  (1, 1312880400, 10),  -- 09.08.2011 12:00:00 -> 1 august
  (2, 1312880410, 20),  -- 09.08.2011 12:00:10 -> 2
  (3, 1312880420, 30),  -- 09.08.2011 12:00:20 -> 3
  (4, 1312880430, 40),  -- 09.08.2011 12:00:30 -> 4
  (5, 1312880440, 50),  -- 09.08.2011 12:00:40 -> 5
  (6, 1315558800, 60),  -- 09.09.2011 12:00:00 -> 1 september
  (7, 1315558810, 70);  -- 09.09.2011 12:00:10 -> 2

SELECT AVG(value) FROM (
  SELECT t1.*, COUNT(*) cnt FROM table1 t1
  LEFT JOIN table1 t2
    ON t2.time_read <= t1.time_read
      AND YEAR(FROM_UNIXTIME(t2.time_read)) = YEAR(FROM_UNIXTIME(t1.time_read))
      AND MONTH(FROM_UNIXTIME(t2.time_read)) = MONTH(FROM_UNIXTIME(t1.time_read))
  GROUP 
    BY time_read
) t
GROUP BY
  YEAR(FROM_UNIXTIME(time_read)), MONTH(FROM_UNIXTIME(time_read)), CEIL(cnt/4);

+------------+
| AVG(value) |
+------------+
|    25.0000 |
|    50.0000 |
|    65.0000 |
+------------+

It does group by month and 4 records in month.