MySQL - How can I always round up decimals?

Question

For instance I have the following value:

0.000018

This is 6 decimal places, but I want to round it up the nearest whole 4th decimal place, so that:

0.000018 -> 0.0001

I've played with the round() funcction but if I simply use the round function:

round(0.000018,4) = 0.0000

When dealing with decimals for financial purposes, in this case one needs to round up and charge the customer instead of giving them a freebie! But round() will go round up or down depending on value, I need to consistently round up.

Is there a simple way of doing this?

He doesn't, because round goes up or down. That's why there is a question :) — Nanne, Oct 09 '11 at 18:24
Sorry, typo there... I meant 0.0001. Corrected in original question. — quidpro, Oct 09 '11 at 18:24

score 33 · Accepted Answer · answered Oct 09 '11 at 18:23

33

You can use ceil (ceiling). It only rounds up, so you'll have to multiply with 10000, do the ceil and then divide the result again.

So ceil(0.000145* 10000) = ceil(1.45) = 2 Divide back and you'll have 0.0002

EDIT: wait, wut? that doesn't work. I mean FLOOR obviously but the working is the same :D The manual is on the same page too :)

So floor(0.000145* 10000) = floor(1.45) = 1 Divide back and you'll have 0.0001

answered Oct 09 '11 at 18:23

Nanne

64,065
16
119
163

1

Thanks, think ceil() is the answer here: ceil(0.000001*10000)/10000 = 0.0001 ceil(0.000011*10000)/10000 = 0.0001 ceil(0.000111*10000)/10000 = 0.0002 ceil(0.000200*10000)/10000 = 0.0002 ceil(0.000201*10000)/10000 = 0.0003 – quidpro Oct 09 '11 at 18:42

score 5 · Answer 2 · answered Jan 08 '16 at 15:08

5

There's another method, which is to add half a multiple of 10. For example: round(x+0.005, 2) where x is 0.923 = 0.93 which reduces the maximum floating point division error.

answered Jan 08 '16 at 15:08

Jack Nord

69
1
7

MySQL's round has quite undefined behavior for inprecise numbers: http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_round (For approximate-value numbers, the result depends on the C library) – Vladislav Rastrusny Mar 02 '16 at 09:01
This will work incorrectly when the value is x.00, it will round up to x.01. – MotoRidingMelon Feb 11 '21 at 22:02

score 0 · Answer 3 · answered Oct 09 '11 at 18:22

0

Use ROUND(X,D), which rounds the value X to D decimal places.

answered Oct 09 '11 at 18:22

Saket

45,521
12
59
79

4

This does not round up as the OP requested. `ROUND(1.11,1)` will go to `1.1` instead of `1.2` – lindon fox Sep 12 '13 at 07:36

MySQL - How can I always round up decimals?

3 Answers3

Linked