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.

score 117 · Accepted Answer · edited Apr 10 '19 at 07:06

117

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 '19 at 07:06

RavatSinh Sisodiya

1,596
1
20
42

answered Jan 04 '12 at 18:39

aleroot

71,077
30
176
213

@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? – NHG Feb 10 '14 at 13:36
4

Could be done as an if eg: `SUM(IF(PaymentType = "credit card", TotalAmount, 0) AS CreditCardTotal,` – George Jun 05 '14 at 14:34
@George: Yes, I believe IF is equivalent. – Mark Doliner Dec 10 '15 at 21:01
@NHG: I suspect that the performance is identical, but I haven't tested. – Mark Doliner Dec 10 '15 at 21:01
3

I just tested with large amount of rows and got same performance – Henri Cavalcante Feb 22 '18 at 14:25
See [MySQL - CASE vs IF Statement vs IF function](https://stackoverflow.com/q/30047983/711006) for syntax and use case differences (no perfomance comparison though). – Melebius Mar 15 '22 at 07:51

score 66 · Answer 2 · answered Jan 04 '12 at 18:40

66

How about this?

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

answered Jan 04 '12 at 18:40

Scott Hunter

48,888
12
60
101

It doesn't work in MsSql. the (IF) statement is doing something else. – Sayed Muhammad Idrees Apr 19 '19 at 19:23
15

@Sayedidrees: OK, but the question was about MySQL. – Scott Hunter Apr 19 '19 at 19:32

MYSQL Sum Query with IF Condition

2 Answers2

Linked

Related