MYSQL Sum Query with IF Condition

Question

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.

RavatSinh Sisodiya · Accepted Answer · 2019-04-10 07:06:10Z

122

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

edited Apr 10, 2019 at 7:06

RavatSinh Sisodiya

1,6161 gold badge21 silver badges42 bronze badges

answered Jan 4, 2012 at 18:39

aleroot

73k32 gold badges182 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

NHG Over a year ago

@aleroot is there any difference between SUM(CASE WHEN .. THEN .. ELSE .. END) and SUM(IF(.., .., ..)) when I would like check only one condition (if some boolean field is 1)? I mean, which one should be faster?

George Over a year ago

Could be done as an if eg: SUM(IF(PaymentType = "credit card", TotalAmount, 0) AS CreditCardTotal,

Mark Doliner Over a year ago

@George: Yes, I believe IF is equivalent.

Mark Doliner Over a year ago

@NHG: I suspect that the performance is identical, but I haven't tested.

Henri Cavalcante Over a year ago

I just tested with large amount of rows and got same performance

|

Scott Hunter · Accepted Answer · 2012-01-04 18:40:16Z

69

How about this?

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

answered Jan 4, 2012 at 18:40

Scott Hunter

50k12 gold badges65 silver badges107 bronze badges

2 Comments

Sayed M. Idrees Over a year ago

It doesn't work in MsSql. the (IF) statement is doing something else.

Scott Hunter Over a year ago

@Sayedidrees: OK, but the question was about MySQL.

Collectives™ on Stack Overflow

MYSQL Sum Query with IF Condition

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related