MySQL将总额与所有费用，收入，储蓄（3个不同的表格）的总和相比较每月的百分比

Question

我试图创建一个查询，以便比较每个月的费用，收入和储蓄的百分比值彼此相连。我正在使用MySQL数据库。

为例的表和查询： http://sqlfiddle.com/#!9/78b360/1

到目前为止，我能够从每个表每个月获得的总和：

SELECT * 
FROM(
SELECT sum(amount) AS Total, month(date) AS Month FROM expenses 
UNION ALL 
SELECT sum(amount), month(date) FROM income 
UNION ALL 
SELECT sum(amount), month(date) FROM savings 
GROUP BY month(date)) as grand_total 

结果：

| Total | Month | 
|---------|-------| 
| 1323232 |  9 | 
| 300101 |  9 | 
| 20000 |  9 | 
|  1 | 10 | 

我试图实现的是这样的东西，如果我只看第9个月：

| Total | Month | Percent 
|---------|-------|-------- 
| 1323232 |  9 |  81% 
| 300101 |  9 |  18% 
| 20000 |  9 |  1% 

你能帮我解决吗？我已经看到了stackoverflow和其他网站上的许多答案，但无法找到我确切需要的。非常感谢您的帮助。谢谢。

Answer 1

您当前的查询存在的问题是，您不是按月份在expenses或income表中进行汇总，仅限于savings表。最后GROUP BY确实不是适用于一切。

我认为你需要在这里做的是首先按月对三个表中的每一个进行汇总，将这些结果联合在一起，然后按月汇总第二次以获得每个月的总计。

SELECT 
    month, 
    SUM(total) AS month_total 
FROM 
(
    SELECT MONTH(date) AS month, SUM(amount) AS total 
    FROM expenses 
    GROUP BY MONTH(date) 
    UNION ALL 
    SELECT MONTH(date), SUM(amount) 
    FROM income 
    GROUP BY MONTH(date) 
    UNION ALL 
    SELECT MONTH(date), SUM(amount) 
    FROM savings 
    GROUP BY MONTH(date) 
) t 
-- WHERE month = 9   -- or whichever months you want to see 
GROUP BY 
    month 
ORDER BY 
    month