Q:

How to SUM() fields in a pivoted table

I have a MySQL table sort of like this
Table 'club_funds'
| Income    | Label                   | Amount  |
+-----------+-------------------------+---------+
| 1         | Membership fees         |    1000 |
| 0         | Gathering party costs   |     500 |
| 1         | Garage sale profit      |     250 |

which I managed to turn into this
| Label                   | Income | Expense |
+-------------------------+--------+---------+
| Membership fees         |   1000 |         |
| Gathering party costs   |        |     500 |
| Garage sale profit      |    250 |         |

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

Now, I want to add a total to the bottom row.
| Label                   | Income | Expense |
+-------------------------+--------+---------+
| Membership fees         |   1000 |         |
| Gathering party costs   |        |     500 |
| Garage sale profit      |    250 |         |
| Total                   |   1250 |     500 |

I've been reading about adding a total row to the bottom of a table, but it involves ROLLUP which is a modifier for GROUP BY. As you can see above, I do not use GROUP BY for this so I can't use ROLLUP (or can I?).
So, I'm thinking of adding this at the end of the query
UNION SELECT 'Total', SUM(Income), SUM(Expense)

but I got this error
Unknown column 'Income' in 'field list'

Any way I can make this work?

A:

I think that its either because you are missing a from in the second select or because you are tring to select from the table it self, which doesn't have columns income and expense yet, because other that , the query is fine.. so try:
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)

A:

You can use ROLLUP if you add a GROUP BY clause:
SELECT COALESCE(Label, 'Total') AS Label,
       SUM(IF (income = 1, amount, null)) AS `Income`,
       SUM(IF (income = 0, amount, null)) AS `Expense`
FROM club_funds
GROUP BY Label WITH ROLLUP

In MySQL you can also simplify your query a bit like this:
SELECT COALESCE(Label, 'Total'),
       SUM((income = 1)*amount) AS `Income`,
       SUM((income = 0)*amount) AS `Expense`
FROM club_funds
GROUP BY Label WITH ROLLUP

Demo here

