过去 12 个月的 Mysql 总和 [英] Mysql sum for last 12 months

问题描述

我正在尝试创建一个查询，以获取基于月份的最近 12 个月的记录以进行图表表示.经过大量阅读和观看此，如果您从一张表中删除一些数据，您就会看到问题.

这是一个可行的解决方案，它可以为您提供所有月份的数据，将两个表中的数据相加，即使只有一个表.这是通过将费用和付款作为单独的查询处理，然后将它们连接在一起来实现的.

SELECT Months.Month, COALESCE(expensedata.ExpenseAmount, 0) AS ExpenseAmount, COALESCE(paymentdata.PaymentAmount, 0) AS PaymentAmount从(SELECT DATE_FORMAT(now(), '%m/%y') AS 月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 月左加入(SELECT SUM(price) AS ExpenseAmount, DATE_FORMAT(date_occurred,'%m/%y') AS Month来自费用哪里 user_id = 1GROUP BY MONTH(date_occurred), YEAR(date_occurred)) 费用数据 ON Months.Month = 费用数据.Month左加入(SELECT SUM(amount) AS PaymentAmount, DATE_FORMAT(date_occurred,'%m/%y') AS Month来自付款哪里 user_id = 1GROUP BY MONTH(date_occurred), YEAR(date_occurred)) Paymentdata ON Months.Month = paymentdata.MonthORDER BY Months.Month;

SQL Fiddle 显示此工作:http://sqlfiddle.com/#!2/3f52a8/5

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

解决方案

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

这篇关于过去 12 个月的 Mysql 总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！