mysql - 如何对数据透视表中的字段求和()

Question

我有一个类似这样的 MySQL 表

Table 'club_funds'
| Income    | Label                   | Amount  |
+-----------+-------------------------+---------+
| 1         | Membership fees         |    1000 |
| 0         | Gathering party costs   |     500 |
| 1         | Garage sale profit      |     250 |

我设法把它变成了这个

| Label                   | Income | Expense |
+-------------------------+--------+---------+
| Membership fees         |   1000 |         |
| Gathering party costs   |        |     500 |
| Garage sale profit      |    250 |         |

使用这个查询

SELECT Label,
IF (income = 1, amount, null) AS `Income`,
IF (income = 0, amount, null) AS `Expense`
FROM club_funds

现在，我想在底行添加总计。

| Label                   | Income | Expense |
+-------------------------+--------+---------+
| Membership fees         |   1000 |         |
| Gathering party costs   |        |     500 |
| Garage sale profit      |    250 |         |
| Total                   |   1250 |     500 |

我一直在阅读有关在表格底部添加总行的信息，但它涉及 ROLLUP，它是 GROUP BY 的修饰符。正如您在上面看到的，我没有为此使用 GROUP BY，所以我不能使用 ROLLUP(或者我可以吗？)。

所以，我正在考虑在查询末尾添加这个

UNION SELECT 'Total', SUM(Income), SUM(Expense)

但是我得到了这个错误

Unknown column 'Income' in 'field list'

我有什么方法可以让它工作吗？

Answer 1

我认为这要么是因为您在第二个选择中缺少一个 from，要么是因为您试图从它自己的表中进行选择，该表还没有收入和费用列，因为除此之外，查询没问题.. 所以尝试:

SELECT Label,
IF (income = 1, amount, null) AS `Income`,
IF (income = 0, amount, null) AS `Expense`
FROM club_funds
UNION
(SELECT 'Total' as `label`,
       sum(case when income = 1 then amount else 0) as `Income`,
       sum(case when income = 0 then amount else 0) as `Expense`
FROM club_funds)