[mysql] 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.

This question is related to mysql if-statement sum

The answer is


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


How about this?

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

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to if-statement

How to use *ngIf else? SQL Server IF EXISTS THEN 1 ELSE 2 What is a good practice to check if an environmental variable exists or not? Using OR operator in a jquery if statement R multiple conditions in if statement Syntax for an If statement using a boolean How to have multiple conditions for one if statement in python Ifelse statement in R with multiple conditions If strings starts with in PowerShell Multiple conditions in an IF statement in Excel VBA

Examples related to sum

Iterating over arrays in Python 3 Get total of Pandas column Pandas: sum DataFrame rows for given columns How to find sum of several integers input by user using do/while, While statement or For statement SQL Sum Multiple rows into one Python Pandas counting and summing specific conditions SELECT query with CASE condition and SUM() Using SUMIFS with multiple AND OR conditions How to SUM parts of a column which have same text value in different column in the same row how to count the total number of lines in a text file using python