title: MySQL - sum column value(s) based on row from the same table
body: <p>I'm trying to get 'Cash', 'Check' and 'Credit Card' totals in new columns based on ProductID from the same table. </p>
  
  <p>Table - Payments</p>
  
  <pre><code>+-----------+------------+---------------+--------+
  | 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 |
  +-----------+------------+---------------+--------+
  </code></pre>
  
  <p>Desired Output -</p>
  
  <pre><code>+------------+------+-------+-------------+-------+
  | ProductID  | Cash | Check | Credit Card | Total |
  +------------+------+-------+-------------+-------+
  |          3 |   20 |    15 |          25 |    60 |
  |          4 |    5 |     6 |           7 |    18 |
  +------------+------+-------+-------------+-------+
  </code></pre>
  
  <p>I've tried LEFT JOINing the same table but haven't had any success. Any suggestions would be appreciated. Thanks.</p>
  
  <p>Unsuccessful and incomplete attempt - </p>
  
  <pre><code>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;
  </code></pre>