Survey Archive

Question

I have the following MySQL query:

select members_categories.category_desc as 'membership_type', SUM( CASE payment_method WHEN 'Bank Transfer' THEN amount_paid ELSE 0 END ) AS 'Bank Transfer', SUM( CASE payment_method WHEN 'Cash' THEN amount_paid ELSE 0 END ) AS 'Cash', SUM( CASE payment_method WHEN 'Cheque' THEN amount_paid ELSE 0 END ) AS 'Cheque', SUM( CASE payment_method WHEN 'Credit Card' THEN amount_paid ELSE 0 END ) AS 'Credit Card', SUM( CASE payment_method WHEN 'Direct Debit' THEN amount_paid ELSE 0 END ) AS 'Direct Debit', SUM( CASE payment_method WHEN 'PayPal' THEN amount_paid ELSE 0 END ) AS 'PayPal', SUM( CASE payment_method WHEN 'Salary Deduction' THEN amount_paid ELSE 0 END ) AS 'Salary Deduction', SUM( CASE payment_method WHEN 'Standing Order' THEN amount_paid ELSE 0 END ) AS 'Standing Order', SUM( amount_paid ) AS 'Total' FROM members_main, members_categories, members_payments WHERE members_categories.category_code=members_main.membership_type and members_main.contact_id=members_payments.contact_id and members_payments.payment_date between '2012-01-01' and '2013-12-31' GROUP BY membership_type With ROLLUP 

What returns:

As you can see above, the final ROLLUP value below shows the description of the member_type field of the last returned row. Is there a way to replace this with the word Total ?

Answer 1

Use IFNULL for this:

  select IFNULL(members_categories.category_desc, 'Total') as 'membership_type', ... GROUP BY membership_type With ROLLUP 

It does what you need. If you were fully compatible with ANSI, you should use

  GROUP BY members_categories.category_desc With ROLLUP 

If you use more than one item in a GROUP BY clause, you need to process them all with IFNULL. For example, from your SqlFiddle. http://sqlfiddle.com/#!2/8818d/16/0

 SELECT IFNULL(product,'GROUP TOTAL') AS product, <--- group by term IFNULL(year, 'YEAR TOTAL') as year, <--- group by term SUM(amount) FROM test_rollup GROUP BY year, product WITH ROLLUP