How to use SUM () fields in a sliding table

Question

I have a MySQL table 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 request

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

Now I want to add the total to the bottom line.

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

I read about adding a common row to the bottom of the table, but it does include ROLLUPthat is a modifier for GROUP BY. As you can see above, I do not use GROUP BYfor this, so I can not use ROLLUP(or can I?).

So I'm going to add this at the end of the request

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

but i got this error

Unknown column 'Income' in 'field list'

Anyway, can I do this job?

Answer 1

You can use ROLLUPif you added a sentence GROUP BY:

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

MySQL :

SELECT COALESCE(Label, 'Total'),
       SUM((income = 1)*amount) AS `Income`,
       SUM((income = 0)*amount) AS `Expense`
FROM club_funds
GROUP BY Label WITH ROLLUP

Answer 2

, - , select, , tring, it self, , , .. :

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)