MySQL Count totals by year and month and Calcul Cumulative count -

i need optimize sql request.

i have table looks this:

id,resgier_date,action 1,'2011-01-01 04:28:21','signup' 2,'2011-01-05 04:28:21','signup' 3,'2011-02-02 04:28:21','signup' 

how select , group these output is:

year,month,total,cumulative_sum 2011,1,2,2 2011,2,1,3 

i like, 1 request, add, 1 column cumulative of count(id).

i try request :

select year(register_date),      count( * ),      @running_total := @running_total + count( * ) cumulative_sum mytable t      join (select @running_total := 0) r group year(register_date) limit 0 , 30 

with no success

thank(s help

try

select year,         month,         total,         @rt := @rt + total cumulative_sum   (         select year(register_date) year,                month(register_date) month,                count(*) total           mytable           group year(register_date), month(register_date)           limit 0, 30        ) n, (select @rt := 0) r 

output

| year | month | total | cumulative_sum | ----------------------------------------- | 2011 |     1 |     2 |              2 | | 2011 |     2 |     1 |              3 | 

sqlfiddle