title: Mysql sum for last 12 months
body: <p>I am trying to create a query to get the last 12 month records based on month for chart representation. After a lot of reading and after watching this <a href="https://stackoverflow.com/questions/19757458/mysql-sum-of-records-by-month-for-the-last-12-months">similar topic</a> I created a query that seems right but missing the months with 0 money. As an example, I see in my graph months 1/14,2/14,4/14 and so on... 3/14 is missing.</p>
  
  <p>My code is this</p>
  
  <pre><code>SELECT *
  FROM
      (SELECT DATE_FORMAT(now(), '%m/%y') AS Month
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 7 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 8 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 9 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 MONTH), '%m/%y')
       UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 11 MONTH), '%m/%y')
      ) AS Months
  LEFT JOIN
      (SELECT sum(expenses.price) AS ExpenseAmount,
              sum(payments.amount) AS PaymentsAmount,
              DATE_FORMAT(expenses.date_occurred,'%m/%y') AS Month,
              DATE_FORMAT(payments.date_occurred,'%m/%y') AS Montha
       FROM expenses,
            payments
       WHERE payments.user_id= 1
           AND payments.user_id=expenses.user_id
       GROUP BY MONTH(payments.date_occurred),
                YEAR(payments.date_occurred)
       ORDER BY payments.date_occurred ASC ) data ON Months.MONTH = data.Montha
  ORDER BY data.Montha;
  </code></pre>
  
  <p>Any help will be great as this kind of queries are too advanced for me :-)</p>
  
  <p><img src="https://i.stack.imgur.com/S6OWF.png" alt="enter image description here"></p>