36246

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 <a href="https://stackoverflow.com/questions/19757458/mysql-sum-of-records-by-month-for-the-last-12-months" rel="nofollow">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.

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 :-)

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/S6OWF.png" data-original="https://i.stack.imgur.com/S6OWF.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

Answer1:

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 <a href="http://sqlfiddle.com/#!2/3f52a8/1" rel="nofollow">http://sqlfiddle.com/#!2/3f52a8/1</a> 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: <a href="http://sqlfiddle.com/#!2/3f52a8/5" rel="nofollow">http://sqlfiddle.com/#!2/3f52a8/5</a>

Answer2:

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... <a href="http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/" rel="nofollow">http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/</a>

hope that helps

Answer3:

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

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

Recommend

  • saving a to-many-relationship
  • Convert SQL Query with Subquery to Laravel query
  • Core Data Model set up
  • Sort by date - Swift 3
  • SQL query to rebuild a table using its dynamic row data for column names
  • Change SQL data temporarily
  • Creating a new column name based on a loop variable and an additional string
  • Kibana - Pie-Chart with sum over two different fields
  • Nested parentheses get string one by one
  • iOS Swift 2.0 UITextView freezes app depending on text
  • Web API - Handling HEAD requests and specify custom Content-Length
  • ZK inner class tree property not readable
  • Java Swing - Translucent Components
  • Average Column-wise by using T-sql and Pivot
  • Calculating subtotals in R
  • jQuery Slick Slider showing some empty slides
  • Show records ordered with maximum price first in PHP & MySQL
  • How to sort a same column both in asc order and desc order
  • Warning: strpos() expects parameter 1 to be string, resource
  • How to Add Polymorphic Comments to Feed?
  • SQL - Select lowest values with group by and order by?
  • Find JSON nested nodes using multiple string values
  • Hector: how to query parts of a Composite Type
  • User messaging system
  • Laravel: Getting Session ID oddly truncates when using foreach
  • converting text file into xml using php?
  • MySQL Order by column = x, column asc?
  • Read text file and split every line in MSBuild
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • javaw.exe and eclipse startup problems
  • TFS: Get latest causes slow project reloading
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Updated Ionic CLI but shows previous version (Windows)
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • embed rChart in Markdown
  • need help with bizarre java.net.HttpURLConnection behavior
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app