compare rows. do something when value changes in a particular column

[izamu]

I want to compare the values within a column and aggregate (count) at each change in value. so for example column "A" has a series of numbers like (1,1,1,2,1,5,2,2,5) the aggregation should be the counts 3,1,1,1,2,1. 3 of 1, followed by 1 of value 2, followed by 1 value 1...

somebody suggested duplicating the target column and shifting in down by one row and then comparing the values between the original column and the new column but I couldn't perform what he suggested.
can anyone help?

[Luuk]

mysql> select i,j from test21;
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
| 2 |    1 |
| 3 |    1 |
| 4 |    2 |
| 5 |    1 |
| 6 |    5 |
| 7 |    2 |
| 8 |    2 |
| 9 |    5 |
+---+------+
 

set @a:=1;
select 
    t1.i I1, 
    t2.i I2, 
    t3.i I3, 
    t1.j J1, 
    t2.j J2, 
    t3.j J3, 
    @a:=IF(t1.j=t2.j,@a+1,1) A 
from test21 t1 
left join test21 t2 
    on t1.i=t2.i+1
left join test21 t3 
    on t1.i=t3.i-1
where (t1.j<>t2.j and t1.j<>t3.j) or (t1.j=t2.j and t1.j<>t3.j) or t3.i is NULL or t2.i is NULL

It almost what you want in column A,
only first two rows are wrong.... ;)

+----+------+------+------+------+------+------+
| I1 | I2   | I3   | J1   | J2   | J3   | A    |
+----+------+------+------+------+------+------+
|  1 | NULL |    2 |    1 | NULL |    1 |    1 |
|  3 |    2 |    4 |    1 |    1 |    2 |    2 |
|  4 |    3 |    5 |    2 |    1 |    1 |    1 |
|  5 |    4 |    6 |    1 |    2 |    5 |    1 |
|  6 |    5 |    7 |    5 |    1 |    2 |    1 |
|  8 |    7 |    9 |    2 |    2 |    5 |    2 |
|  9 |    8 | NULL |    5 |    2 | NULL |    1 |
+----+------+------+------+------+------+------+