MySQL monthly Sale of last 12 months including months with no Sale

Thanks for @pankaj hint, Here i resolved it via this query...

SELECT     SUM(IF(month = 'Jan', total, 0)) AS 'Jan',    SUM(IF(month = 'Feb', total, 0)) AS 'Feb',    SUM(IF(month = 'Mar', total, 0)) AS 'Mar',    SUM(IF(month = 'Apr', total, 0)) AS 'Apr',    SUM(IF(month = 'May', total, 0)) AS 'May',    SUM(IF(month = 'Jun', total, 0)) AS 'Jun',    SUM(IF(month = 'Jul', total, 0)) AS 'Jul',    SUM(IF(month = 'Aug', total, 0)) AS 'Aug',    SUM(IF(month = 'Sep', total, 0)) AS 'Sep',    SUM(IF(month = 'Oct', total, 0)) AS 'Oct',    SUM(IF(month = 'Nov', total, 0)) AS 'Nov',    SUM(IF(month = 'Dec', total, 0)) AS 'Dec',    SUM(total) AS total_yearly    FROM (SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as totalFROM cartWHERE date <= NOW() and date >= Date_add(Now(),interval - 12 month)GROUP BY DATE_FORMAT(date, "%m-%Y")) as sub

Consider the following table

mysql> select * from cart ;+------+------------+-------------+| id   | date       | total_price |+------+------------+-------------+|    1 | 2014-01-01 |          10 ||    2 | 2014-01-20 |          20 ||    3 | 2014-02-03 |          30 ||    4 | 2014-02-28 |          40 ||    5 | 2014-06-01 |          50 ||    6 | 2014-06-13 |          24 ||    7 | 2014-12-12 |          45 ||    8 | 2014-12-18 |          10 |+------+------------+-------------+

Now as per the logic you are looking back one year and december will appear twice in the result i.e. dec 2013 and dec 2014 and if we need to have a separate count for them then we can use the following technique of generating dynamic date range MySql Single Table, Select last 7 days and include empty rows

t1.month,t1.md,coalesce(SUM(t1.amount+t2.amount), 0) AS totalfrom(  select DATE_FORMAT(a.Date,"%b") as month,  DATE_FORMAT(a.Date, "%m-%Y") as md,  '0' as  amount  from (    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c  ) a  where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)  group by md)t1left join(  SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as amount ,DATE_FORMAT(date, "%m-%Y") as md  FROM cart  where Date <= NOW() and Date >= Date_add(Now(),interval - 12 month)  GROUP BY md)t2on t2.md = t1.md group by t1.mdorder by t1.md;

Output will be

+-------+---------+-------+| month | md      | total |+-------+---------+-------+| Jan   | 01-2014 |    30 || Feb   | 02-2014 |    70 || Mar   | 03-2014 |     0 || Apr   | 04-2014 |     0 || May   | 05-2014 |     0 || Jun   | 06-2014 |    74 || Jul   | 07-2014 |     0 || Aug   | 08-2014 |     0 || Sep   | 09-2014 |     0 || Oct   | 10-2014 |     0 || Nov   | 11-2014 |     0 || Dec   | 12-2013 |     0 || Dec   | 12-2014 |    55 |+-------+---------+-------+13 rows in set (0.00 sec)

And if you do not care about the above case i.e. dec 2014 and dec 2013

Then just change the group by in dynamic date part as

where a.Date <= NOW() and a.Date >= Date_add(Now(),interval - 12 month)  group by month

and final group by as group by t1.month

Month wise saleUse Count to count month wise data.

SELECT DATE_FORMAT(date, "%b") AS month, COUNT(total_price) as totalFROM cartWHERE date <= NOW()and date >= Date_add(Now(),interval - 12 month)GROUP BY DATE_FORMAT(date, "%m-%Y")