MySQL - sum column value(s) based on row from the same table

Question

I'm trying to get 'Cash', 'Check' and 'Credit Card' totals in new columns based on ProductID from the same table.

Table - Payments

+-----------+------------+---------------+--------+ | ProductID |  SaleDate  | PaymentMethod | Amount | +-----------+------------+---------------+--------+ |         3 | 2012-02-10 | Cash          |     10 | |         3 | 2012-02-10 | Cash          |     10 | |         3 | 2012-02-10 | Check         |     15 | |         3 | 2012-02-10 | Credit Card   |     25 | |         4 | 2012-02-10 | Cash          |      5 | |         4 | 2012-02-10 | Check         |      6 | |         4 | 2012-02-10 | Credit Card   |      7 | +-----------+------------+---------------+--------+ 

Desired Output -

+------------+------+-------+-------------+-------+ | ProductID  | Cash | Check | Credit Card | Total | +------------+------+-------+-------------+-------+ |          3 |   20 |    15 |          25 |    60 | |          4 |    5 |     6 |           7 |    18 | +------------+------+-------+-------------+-------+ 

I've tried LEFT JOINing the same table but haven't had any success. Any suggestions would be appreciated. Thanks.

Unsuccessful and incomplete attempt -

SELECT P.ProductID, Sum( PCash.Amount ) AS 'Cash', SUM( PCheck.Amount ) AS 'Check', SUM( PCredit.Amount) AS 'Credit Card'  FROM Payments AS P  LEFT JOIN Payments AS PCash ON P.ProductID = PCash.ProductID AND PCash.PaymentMethod = 'Cash' LEFT JOIN Payments AS PCheck ON P.ProductID = PCheck.ProductID AND PCheck.PaymentMethod = 'Check' LEFT JOIN Payments AS PCredit ON P.ProductID = PCredit.ProductID AND PCredit.PaymentMethod = 'Credit' WHERE P.SaleDate = '2012-02-10' GROUP BY ProductID; 

Answer 1

I think you're making this a bit more complicated than it needs to be.

SELECT     ProductID,     SUM(IF(PaymentMethod = 'Cash', Amount, 0)) AS 'Cash',     -- snip     SUM(Amount) AS Total FROM     Payments WHERE     SaleDate = '2012-02-10' GROUP BY     ProductID