MySQL - Total count & rate of change in a single SQL query -

i'm trying extract total number of website hits plus rate of change(rate of increase/decrease) per client per city per day percentage in single sql query not able right.

i've created example @

http://www.sqlfiddle.com/#!9/fd279/8

could please request assistance?

try avoiding inner join, has wrong condition anyway (t1.hitdate = t2.hitdate , t1.hitdate-1 = t2.hitdate can't satisfied):

select    t1.hitdate,   t1.city,   t1.client,   sum(t1.numvisits)  -   ifnull((select sum(t2.numvisits) page_hits t2 t2.hitdate = t1.hitdate-1 , t2.city = t1.city , t2.client = t1.client), 0)  rate_of_change   page_hits t1 t1.client='c'  group    hitdate,    city    order hitdate;