Search code examples
mysqlroundingdecimal-point

mysql query to round based specific decimal values

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.

Solution

  • 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