How to have a range in a MySQL If statement?

Question

I'm trying to make a select that calculates affiliate payouts.

my approach is pretty simple.

SELECT
 month(payments.timestmap)
,sum(if(payments.amount>=29.95,4,0)) As Tier4
,sum(if(payments.amount>=24.95<=29.94,3,0)) As Tier3
,sum(if(payments.amount>=19.95<=24.94,2,0)) As Tier2
FROM payments
GROUP BY month(payments.timestamp)

The above does not work because MySQL is not evaluating the second part of the condition. Btw it does not cause a syntax error and the select will return results.

Before the above I tried what I was assuming would work like "amount between 24.94 AND 29.94" this caused an error. so then I tried "amount >= 24.94 AND <= 29.94"

So is it possible to have a range comparison using IF in MySql?

Answer 1

The second part of the expression evaluates when you use AND -

SELECT
 month(payments.timestmap)
,sum(if(payments.amount>=29.95,4,0)) As Tier4
,sum(if(payments.amount>=24.95 AND payments.amount<=29.94,3,0)) As Tier3
,sum(if(payments.amount>=19.95 AND payments.amount<=24.94,2,0)) As Tier2
FROM payments
GROUP BY month(payments.timestamp)

I'm not entirely sure why the between clause didn't work for you, but the above should do the job.

Answer 2

There is always the BETWEEN...AND... operator in MySQL.

Answer 3

What error did your first attempt give you? It should definitely work. However, note that the second form you have is incorrect syntax. It should be amount >= 24.94 and amount <= 29.94.