How to short a sql case query that uses between

user_9814550 0
Viewed 45

Hi have this sql query:

SELECT CASE
   WHEN money BETWEEN 0 and 1000 THEN '1000'
   WHEN money BETWEEN 1000 and 2000 THEN '2000'
   WHEN money BETWEEN 2000 and 3000 THEN '3000'
   .
   .
   .
   .
   .
   WHEN money BETWEEN 19000 and 20000 THEN '20000'

from bank;

How can I short this query so I don't use a lot of case statements?

case mysql sql
2 Answers

You can try using mod subtracting the remainder t from you value by division for 1000

SELECT money - MOD( money , 1000 )
FROM bank;

or as suggested by @DM you can also use

FLOOR((money + 999) / 1000) * 1000

the difference should be the same .. both involve function .. but there are nit where condition so this is not in contrast with indexing

user_35223120

I would suggest using the expression:

ceiling(money / 1000)

The only caveat is that 0 goes into its own group. To me, that seems like a feature. But if you don't agree, you could use a case expression or arcane arithmetic:

ceiling((money - (money = 0)) / 1000)
user_11440350
Related