IF条件下的MYSQL求和查询 [英] MYSQL Sum Query with IF Condition

问题描述

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

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.

这是查询：

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

When I try to add the below conditional SUM statement:

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

此条件IF语句失败。

This conditional IF statement fails out.

我有一个名为'TotalAmount'的列和一个名为'PaymentType'的列我希望每天创建一个信用卡交易的SUM，每天检查交易，每天检查paypal交易的总和。
我试图创建一个子查询，但这会返回整个TotalAmount列的值，而不是按天分解。

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.

推荐答案

以这种方式尝试使用 CASE ：

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

应该给你想要的东西......

Should give what you are looking for ...

这篇关于IF条件下的MYSQL求和查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！