我正在为一份具有多个IF条件的报告构建查询。在对SUM进行多个IF条件的操作时,我遇到了问题。
以下是查询:
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
这个查询运行得很好。
当我尝试添加下面的条件SUM语句:
SUM('TotalAmount'(PaymentType = "credit card", 1,0)) AS CreditCardTotal,
这个条件语句不起作用。
我有一个名为'TotalAmount'的列和一个名为'PaymentType'的列,我想创建每天信用卡交易总额、每天支票交易总额和每天PayPal交易总额的总和。 我尝试创建一个子查询,但这会返回整个TotalAmount列的值,而不是按每天细分。
SUM(CASE WHEN .. THEN .. ELSE .. END)
和SUM(IF(.., .., ..))
之间有什么区别吗?我的意思是,哪一个更快? - NHGSUM(IF(PaymentType = "信用卡", TotalAmount, 0) AS 信用卡总额,
- George