mysql query to round based specific decimal values

235 views Asked by At

Can I round up value based specific decimal values,I have this data in my mysql table :

1122.46
4599.99
2000.56
5249.99

and I want query the result like this :

1122.46
4600
2000.56
5250

where only values which have .99 decimal point is rounded up.

2

There are 2 answers

0
Manuel Otto On BEST ANSWER

The only way I could think of is:

SELECT `number` + (FLOOR(`number` + 0.01)-FLOOR(`number`))*0.01

Testing it:

5249.99 + (FLOOR(5249.99 + 0.01)-FLOOR(5249.99))*0.01 = 5250

1122.46 + (FLOOR(1122.46 + 0.01)-FLOOR(1122.46))*0.01 = 1122.46

0
Bohemian On

You could use a case:

select number + case when number - floor(number) = .99 then .01 else 0 end