带有 IF 条件的 MYSQL Sum 查询

本文介绍了带有 IF 条件的 MYSQL Sum 查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在为在 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"的列我希望创建每天信用卡交易的总和、每天支票交易的总和、贝宝交易的总和每一天，.我尝试创建一个子查询，但这会返回整个 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 以这种方式:

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 ...