Get the average of groups of n rows with MySQL

I'm currently stuck trying to get the average value of groups of n rows using MySQL.

I have a MySQL table (data_conso) composed of columns in the following format : id (int); date(datetime); data(int)

I'd like (in order to make a nice graph without too many points) to split all these values by groups of let's say 100 and then get the average value of each of these groups.

With a bit of search and tinkering, I managed to write the following query :

SET @i := 0;
SELECT
    @i:=@i+1 as rownum,
    FLOOR(@i/100) AS `datagrp`,
    AVG(`tmptbl`.`data`)
FROM (
    SELECT `data`
    FROM data_conso ORDER BY `date` ASC
) as `tmptbl`
GROUP BY `datagrp`

Which in theory would work (or at least I don't know why it wouldn't) but only returns one value ! What is very strange is if I remove the AVG开发者_如何学Go() function around tmptbl.data, it returns every group as it should, just without the averaged value.

What I don't understand is why AVG(), which is an aggregate function, doesn't use the GROUP BY in order to make its calculations.

I am really frustrated by this issue and any kind of help would really be appreciated. Forgive me for my english and thanks in advance for your answer !

---

SET @i := 0;
SELECT AVG(`date`), AVG(`data`)
FROM
(
    SELECT
        @i:=@i+1 as rownum,
        FLOOR(@i/100) AS `datagrp`,
        `date`,
        `data`
    FROM data_conso
    ORDER BY `date` ASC
)
GROUP BY `datagrp`;

Something like that should work, the idea is to append the column datagrp to your original table, and then just select the average for each datagrp.

---

Try changing GROUP BY datagrp to GROUP BY tmptbl.data

SET @i := 0;
SELECT
    @i:=@i+1 as rownum,
    FLOOR(@i/100) AS `datagrp`,
    AVG(`tmptbl`.`data`)
FROM (
    SELECT `data`
    FROM data_conso ORDER BY `date` ASC
) as `tmptbl`
GROUP BY `tmptbl`.`data`

---

Just a guess. What if you try:

SET @i := 0;
SELECT
    floor((@i := @i + 1)/100) AS `datagrp`,
    AVG(`tmptbl`.`data`)
FROM (
    SELECT `data`
    FROM data_conso ORDER BY `date` ASC
) as `tmptbl`
GROUP BY `datagrp`

---

This doc sounds like your problem.

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected