带有 IF 条件的 MYSQL 求和查询

MYSQL Sum Query with IF Condition

提问by Matthew Colley

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.

我正在为 SUM 上具有多个 IF 条件的报告构建查询。我在 SUM 上遇到多个 IF 条件的问题。

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 语句时：

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

This conditional IF statement fails out.

此条件 IF 语句失败。

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.

我有一个名为“TotalAmount”的列和一个名为“PaymentType”的列我希望创建每天的信用卡交易总和、每天的支票交易总和、每天的贝宝交易总和，。我试图创建一个子查询，但这会返回整个 TotalAmount 列的值，而不是按天细分。

回答by aleroot

Try with a CASEin this way :

以这种方式尝试使用CASE：

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

Should give what you are looking for ...

应该给你正在寻找的东西......

回答by Scott Hunter

How about this?

这个怎么样？

SUM(IF(PaymentType = "credit card", totalamount, 0)) AS CreditCardTotal