How to have a range in a MySQL If statement?

Question

I am trying to make a choice that calculates the payouts of affiliates.

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 does not evaluate the second part of the condition. Btw does not cause a syntax error, and the selection returns the results.

Before above, I tried what I assumed would work as " amount between 24.94 AND 29.94", which caused an error. so 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 is evaluated when using 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)

, between , .

Answer 2

MySQL BETWEEN... AND... MySQL.

Answer 3

What mistake did your first attempt make? This should definitely work. However, note that the second form you have is the wrong syntax. He must be amount >= 24.94 and amount <= 29.94.

Answer 4

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

This should also work, I tested with both BETWEEN and s> so that they work the same in one of my queries. So yes, both work.