I have the following SQL code:
select distinct members_main.membership_type,
payment_method,sum(amount_paid)
from members_main, members_payments
where members_main.contact_id=members_payments.contact_id
group by members_main.membership_type,payment_method
That returns the following:
I want to be able to return the results in the following table grid format:
Does anyone know if or how I can do this within MySQL?
Thanks,
John
MySQL does not have a pivot function but you can use a CASE expression and an aggregate function to turn the rows into columns:
select m.membership_type,
sum(case when p.payment_method = 'Cash' then amount_paid else 0 end) Cash,
sum(case when p.payment_method = 'Credit Card' then amount_paid else 0 end) CreditCard,
sum(case when p.payment_method = 'Paypal' then amount_paid else 0 end) Paypal
from members_main m
inner join members_payments p
on m.contact_id=p.contact_id
group by m.membership_type;
If you are going to have an unknown number of payment_method's, then you will want to look at using a prepared statement to generate dynamic SQL:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(CASE WHEN p.payment_method = ',
payment_method,
' THEN amount_paid else 0 END) AS `',
payment_method, '`'
)
) INTO @sql
FROM members_payments ;
SET @sql
= CONCAT('SELECT m.membership_type, ', @sql, '
from members_main m
inner join members_payments p
on m.contact_id=p.contact_id
group by m.membership_type');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Depending on data complexity either go with bluefeet's way or if the payment_method amount is dynamic then it sounds like you need a pivot table. Try looking at this.
