How to round up any value in SQL?

Question

Hi,

I haven't found this for SQL yet, please, help me with rounding up the value this way:

45.01 rounds up to 46. Also 45.49 rounds to 46. And 45.99 rounds up to 46, too. I want everything up one whole digit.

How do I achieve this into Update statement like Update product SET price=Round statement ?

Answer 1

Try ceiling...

SELECT Ceiling(45.01), Ceiling(45.49), Ceiling(45.99)

http://en.wikipedia.org/wiki/Floor%5Fand%5Fceiling%5Ffunctions

Answer 2

You could use the ceil function, at least on MySQL ; this portion of SQL code :

select ceil(45.01), ceil(45.49), ceil(45.99);

will get you "46" each time.

For your update, so, I'd say :

Update product SET price = ceil(45.01)

BTW : On MySQL, ceil is an alias to ceiling ; not sure about other DB systems, so you might have to use one or the other, depending on the DB you are using...

Quoting the documentation :

CEILING(X)

Returns the smallest integer value not less than X.

And the given example :

mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1

Answer 3

This depends on the database server, but it is often called something like CEIL or CEILING. For example, in MySQL...

mysql> select ceil(10.5);
+------------+
| ceil(10.5) |
+------------+
|         11 | 
+------------+

You can then do UPDATE PRODUCT SET price=CEIL(some_other_field);

Answer 4

If you want to round off then use the round function. Use ceiling function when you want to get the smallest integer just greater than your argument.

For ex: select round(843.4923423423,0) from dual gives you 843 and

select round(843.6923423423,0) from dual gives you 844