Mysql sum for last 12 months

Question

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 similar topic 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.

My code is this

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;

Any help will be great as this kind of queries are too advanced for me :-)

enter image description here

What does the output of the inline view (that you've aliased as "data") look like when run separately on its own? Is the output you're getting correct other than the missing months? — Brian DeMilia, Aug 13 '14 at 00:06
(Example) In the picture of the graph I should get the month 4/14 with 0. But there is no such thing in the resulting query so it is skipped. The query is correct, indeed there are no data for this month. But I want to have a null value there and the month as is. Let me do this @Brian DeMilia and I'll post it to you — George D., Aug 13 '14 at 08:09

Richard · Accepted Answer · 2014-08-13T08:46:21.560

As the query looks like it should produce a row for each month, can you check the query output, rather than what your graph is producing? I suspect you've got an entry for 04/14, but that the value is NULL rather than 0. To correct this, you can change the query to start

SELECT Months.Month, 
    COALESCE(data.ExpenseAmount, 0) AS ExpenseAmount, 
    COALESCE(data.PaymentAmount, 0) AS PaymentAmount

COALESCE will give you 0 instead of NULL where there are no rows matching your left join.

However, there are further problems in your query. You will only get rows if there is an expense and a payment in the same month - check http://sqlfiddle.com/#!2/3f52a8/1 and you'll see the problem if you remove some of the data from one table.

Here's a working solution which will give you all months, summing the data from both tables, even if only one is present. This works by handling the expenses and payments as separate queries, then joining them together.

SELECT Months.Month, COALESCE(expensedata.ExpenseAmount, 0) AS ExpenseAmount, COALESCE(paymentdata.PaymentAmount, 0) AS PaymentAmount
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(price) AS ExpenseAmount, DATE_FORMAT(date_occurred,'%m/%y') AS Month
   FROM expenses 
   WHERE user_id = 1
   GROUP BY MONTH(date_occurred), YEAR(date_occurred)) expensedata ON Months.Month = expensedata.Month

LEFT JOIN 
  (SELECT SUM(amount) AS PaymentAmount, DATE_FORMAT(date_occurred,'%m/%y') AS Month
   FROM payments
   WHERE user_id = 1
   GROUP BY MONTH(date_occurred), YEAR(date_occurred)) paymentdata ON Months.Month = paymentdata.Month

ORDER BY Months.Month;

SQL Fiddle showing this working: http://sqlfiddle.com/#!2/3f52a8/5

I var_dump() the output WITHOUT your fix. Problem seems to be that it actually doesn't create a row for each month http://laravel.io/bin/5We82 — George D., Aug 13 '14 at 08:31
Richard I am not sure where exactly to put your proposal. I should get a sum(), right? — George D., Aug 13 '14 at 08:40
In questions like this, it's a lot easier for people to help you if you provide all of the information, especially in the form of a link to SQL Fiddle including some sample data. Otherwise we have to guess what you're trying to work with! I've had a guess, and updated my answer. — Richard, Aug 13 '14 at 08:48
Oh my. Brilliant query richard, learned a lot from this and it was exactly what I wanted to do, good guess! Only problem is that the 2013 months are at the end of the results. How can I short them to be first? — George D., Aug 13 '14 at 09:07
The simplest option is to reformat your year as %y-%m, which will produce 2013-01 etc, which sorts alphabetically. If you want to format the month differently, you'll need your months query to include the actual year and month as separate values, so you can use these in the order by statement. — Richard, Aug 13 '14 at 09:14
Or a perhaps simpler (but not very clean) solution is to remove the order by statement and change the order of the rows in your main query. — Richard, Aug 13 '14 at 09:16

score 0 · Answer 2 · answered Aug 13 '14 at 00:10

0

i think your problem is the left join. the result of the select statement inside your left join does not contain any results with 0 money ... it looks like your

where payments.user_id = 1

is the matter...

under the following link you can find an explanation to all types of joins... http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

hope that helps

answered Aug 13 '14 at 00:10

user934801

1,119
1
12
30

The query is correct, indeed there are no data for this month. But I want to have a null value there and the month as is. – George D. Aug 13 '14 at 08:10

score 0 · Answer 3 · answered Aug 13 '14 at 01:50

0

Replace ORDER BY data.Montha to ORDER BY Months.MONTH

There is no '3/14' in data.Montha.

answered Aug 13 '14 at 01:50

Jaugar Chang

3,176
2
13
23

Your original statement will not cause any month missing. There must some thing else. You should post your enviroment information such as db version and os version. – Jaugar Chang Aug 13 '14 at 09:01

Mysql sum for last 12 months

3 Answers3

Linked