MYSQL Sum Query with IF Condition
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.
Solution 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 ...
Solution 2:
How about this?
SUM(IF(PaymentType = "credit card", totalamount, 0)) AS CreditCardTotal