mysql相同行对应的列求和,MySQL - 基于来自同一个表的行对列值进行求和

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;

解决方案

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