title: How to SUM() fields in a pivoted table
body: <p>I have a MySQL table sort of like this</p>
  
  <pre><code>Table 'club_funds'
  | Income    | Label                   | Amount  |
  +-----------+-------------------------+---------+
  | 1         | Membership fees         |    1000 |
  | 0         | Gathering party costs   |     500 |
  | 1         | Garage sale profit      |     250 |
  </code></pre>
  
  <p>which I managed to turn into this</p>
  
  <pre><code>| Label                   | Income | Expense |
  +-------------------------+--------+---------+
  | Membership fees         |   1000 |         |
  | Gathering party costs   |        |     500 |
  | Garage sale profit      |    250 |         |
  </code></pre>
  
  <p>using this query</p>
  
  <pre><code>SELECT Label,
  IF (income = 1, amount, null) AS `Income`,
  IF (income = 0, amount, null) AS `Expense`
  FROM club_funds
  </code></pre>
  
  <p>Now, I want to add a total to the bottom row.</p>
  
  <pre><code>| Label                   | Income | Expense |
  +-------------------------+--------+---------+
  | Membership fees         |   1000 |         |
  | Gathering party costs   |        |     500 |
  | Garage sale profit      |    250 |         |
  | Total                   |   1250 |     500 |
  </code></pre>
  
  <p>I've been reading about adding a total row to the bottom of a table, but it involves <code>ROLLUP</code> which is a modifier for <code>GROUP BY</code>. As you can see above, I do not use <code>GROUP BY</code> for this so I can't use <code>ROLLUP</code> (or can I?).</p>
  
  <p>So, I'm thinking of adding this at the end of the query</p>
  
  <pre><code>UNION SELECT 'Total', SUM(Income), SUM(Expense)
  </code></pre>
  
  <p>but I got this error</p>
  
  <pre><code>Unknown column 'Income' in 'field list'
  </code></pre>
  
  <p>Any way I can make this work?</p>