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

User · Answer

This might be seen as a little complex but does exactly what you want

SELECT 
  DISTINCT(p.`ProductID`) AS ProductID,
  SUM(pl.CashAmount) AS Cash,
  SUM(pr.CashAmount) AS `Check`,
  SUM(px.CashAmount) AS `Credit Card`,
  SUM(pl.CashAmount) + SUM(pr.CashAmount) +SUM(px.CashAmount) AS Amount
FROM
  `payments` AS p 
  LEFT JOIN (SELECT ProductID,PaymentMethod , IFNULL(Amount,0) AS CashAmount FROM payments WHERE PaymentMethod = 'Cash' GROUP BY ProductID , PaymentMethod ) AS pl 
    ON pl.`PaymentMethod` = p.`PaymentMethod` AND pl.ProductID = p.`ProductID`
  LEFT JOIN (SELECT ProductID,PaymentMethod , IFNULL(Amount,0) AS CashAmount FROM payments WHERE PaymentMethod = 'Check' GROUP BY ProductID , PaymentMethod) AS pr 
    ON pr.`PaymentMethod` = p.`PaymentMethod` AND pr.ProductID = p.`ProductID`
  LEFT JOIN (SELECT ProductID, PaymentMethod , IFNULL(Amount,0) AS CashAmount FROM payments WHERE PaymentMethod = 'Credit Card' GROUP BY ProductID , PaymentMethod) AS px 
    ON px.`PaymentMethod` = p.`PaymentMethod` AND px.ProductID = p.`ProductID`
GROUP BY p.`ProductID` ;

Output

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

SQL Fiddle Demo

User · Answer

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

User · Answer

SUM CASE using example      SELECT    DISTINCT p  ProductID   AS ProductID    SUM IF p  PaymentMethod   Cash  Amount 0   AS Cash     SUM IF p  PaymentMethod   Check  Amount 0   AS Check     SUM IF p  PaymentMethod   Credit Card  Amount 0   AS Credit Card     SUM  CASE PaymentMethod        WHEN  Cash  THEN Amount       WHEN  Check  THEN Amount       WHEN  Credit Card  THEN Amount      END  AS Total FROM    payments  AS p  GROUP BY p  ProductID    SQL FIDDLE  http   www sqlfiddle com   9 23d07d 18

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

SQL Fiddle Demo

Examples related to mysql

Examples related to select

Examples related to group-by

Examples related to left-join

Examples related to pivot