fetch data grouped by month using mysql interval -

i want records last 1 year purchase table grouped month. important mention there no purchase in last 2 month including current. mysql query following

select year(purchasedate) y ,month(purchasedate) m, count(*) purchase productid=1001 , purchasedate >= curdate() - interval 1 year group m, y 

it return 11 months data last purchase date

2015 1 9177 2015 2 9914 2015 3 11672 2015 4 10521 2015 5 11873 2015 6 10449 2015 7 4057 2014 9 340 2014 10 9913 2014 11 8451 2014 12 7857 

but should return 12 months data.

    2015 1 9177     2015 2 9914     2015 3 11672     2015 4 10521     2015 5 11873     2015 6 10449     2015 7 4057     2015 8 0     2015 9 0     2014 10 9913     2014 11 8451     2014 12 7857 

what doing wrong. in advance.

try this

select d.y, d.m, p.cnt       (select a.y, b.m      (select 2015 y union select 2014 y)     cross join (select 1 m union select 2 m union select 3 m union select 4 m union select 5 m union select 6 m union select 7 m union select 8 m union select 9 m union select 10 m union select 11 m union select 12 m) b     concat(a.y, '-', b.m, '-01') between curdate() - interval 13 month , curdate() - interval 1 month     order 1, 2) d     left outer join     (select year(purchasedate) y ,month(purchasedate) m, count(*) cnt purchase productid=1001 , purchasedate >= curdate() - interval 1 year group m, y) p on d.m=p.m , d.y=p.y order 1 desc, 2 

you not doing wrong, mysql cannot return rows months not exist in data-set. solve problem, need provide months self (table d in query) , left join them results data-set