get previous 12 month data and if not exist then add zero in mysql

###Table                        ###Expected Outpoot

ID |    create_date                 Month | Application | Year
------------------------------      --------------------------
1  |    2017-06-25 10:00:11         10   |      0       | 2016
2  |    2017-06-26 10:00:11         11   |      0       | 2016
3  |    2017-07-02 10:00:11         12   |      0       | 2016
4  |    2017-07-25 10:00:11         1    |      0       | 2017
5  |    2017-08-21 10:00:11         2    |      0       | 2017
6  |    2017-08-22 10:00:11         3    |      0       | 2017
7  |    2017-08-25 10:00:11         4    |      0       | 2017
                                    5    |      0       | 2017
                                    6    |      2       | 2017
                                    7    |      2       | 2017
                                    8    |      3       | 2017
                                    9    |      0       | 2017

I am Trying to get the monthly data count from my table.

I am close to solve the issue.Here is the query and result.

select date_format(tn.create_date,'%Y-%m') as mon,
count(*) as num
FROM table_name as tn
GROUP BY mon order by mon;

Month       |   Application 
--------------------------------
2017-06     |       2       
2017-07     |       2       
2017-08     |       3

So, How do i get my expected output?

Here is the Query Fiddle

• Build a table of all months.

• LEFT JOIN to that table. Viola, the zeros show up.

Well, you might get NULLs instead of zeros. So use the IFNULL(application, 0) function.

BTW, MariaDB has a nifty way to generate sequences of numbers; see "seq".

StackExchange Database Administrators Q#185079, answer score: 4