MySQL query to get last 12 months data grouped by month including zero counts

I have table orders (order_id, order_processed_date). I want the count of orders per month for last 12 months. If any month has 0 orders, then it should print 0.

Something like this:

Total Orders | Month
-----------------------
2            | OCT 2018
3            | SEP 2018
0            | AUG 2018
0            | JUL 2018
1            | JUN 2018
0            | MAY 2018
0            | APR 2018
0            | MAR 2018
5            | FEB 2018
5            | JAN 2018
0            | DEC 2017
0            | NOV 2017

I already searched in this website and get some queries but did not find exact solution for this. I get result using below query but it does not have year:

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'
FROM (SELECT DATE_FORMAT(order_processed_date, '%b') AS month, 
COUNT(order_id) as total
FROM orders
WHERE order_processed_date <= NOW() and order_processed_date >= 
Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(order_processed_date, '%m-%Y')) as sub

Can please someone help me to give me result as I required.

Answer

The following query builds a list of 1st day of month for previous 12 months. For example on Oct 19 2018 the list will contain dates from Oct 01 2018 to Nov 01 2017. Left join does the rest:

SELECT date, COUNT(orders.primary_key)
FROM (
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS date UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 3 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 4 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 5 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 6 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 7 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 8 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 9 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 10 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 11 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 12 MONTH
) AS dates
LEFT JOIN orders ON order_processed_date >= date AND order_processed_date < date + INTERVAL 1 MONTH
GROUP BY date