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