MYSQL Sum Query with IF Condition

Question

I am building a query for a report with multiple IF conditions on the SUM. I am having problems with a multiple IF conditions on the SUM.

Here is the query:

SELECT SUM(`totalamount`) AS Total,  SUM(`PayPalFee`) AS Fees, DATE(`TransactionDate`) AS `Day`,  SUM(IF(PaymentType = "paypal", 1,0)) AS Paypal,  SUM(IF(PaymentType = "check", 1,0)) AS Checks,  SUM(IF(PaymentType = "credit card", 1,0)) AS CreditCard,  COUNT(*) AS Entries  FROM my_table  WHERE TransactionDate between '2011-05-05' AND '2012-01-30'  GROUP BY day  ORDER BY `day` ASC 

This query works just fine.

When I try to add the below conditional SUM statement:

 SUM('TotalAmount'(PaymentType = "credit card", 1,0)) AS CreditCardTotal, 

This conditional IF statement fails out.

I have a column called 'TotalAmount' and a column called 'PaymentType' I am looking to create a SUM of the credit card transactions by each day, a SUM of the checks transactions by each day, a SUM of the paypal transactions by each day,. I have tried to create a subquery but this returns a value for the entire TotalAmount column, not broken down by day.

Answer 1

Try with a CASE in this way :

SUM(CASE      WHEN PaymentType = "credit card"      THEN TotalAmount      ELSE 0  END) AS CreditCardTotal, 

Should give what you are looking for ...