MySQL COUNT() on groups returns wrong number of rows

Question

I have this table called "values":

value
12
13
5
56
3
56
79
98
58
74
52
2
8
32
4

I want to get the lowest value for each block of 5, so I tried this query:

SET @a = -1;
SELECT FLOOR((@a:=@a+1)/5) AS block, MIN(value)
FROM values
GROUP BY block

It seems like this query does not include the last row in each block, so I tried count:

SET @a = -1;
SELECT FLOOR((@a:=@a+1)/5) AS block, COUNT(value)
FROM values
GROUP BY block

which returned:

block    COUNT(value)
0        4
1        4
2        4

what is happening here?

Answer 1

Try this:

SELECT FLOOR(id/5) AS block, MIN(`value`)
FROM (SELECT (@id:=@id+1) AS id, `value`
      FROM `values`, (SELECT @id:=-1) AS A) AS B
GROUP BY block;