25

I'm running SQL that needs rounding up the value to the nearest whole number.

What I need is 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 in an UPDATE statement like the following?

Update product SET price=Round
Community
  • 1
  • 1
Skuta
  • 5,830
  • 27
  • 60
  • 68

7 Answers7

36

You could use the ceiling function; this portion of SQL code :

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

will get you "46" each time.

For your update, so, I'd say :

Update product SET price = ceiling(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
Henrik Høyer
  • 1,225
  • 1
  • 19
  • 27
Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
16

Try ceiling...

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

http://en.wikipedia.org/wiki/Floor_and_ceiling_functions

pjp
  • 17,039
  • 6
  • 33
  • 58
6

For MS SQL CEILING(your number) will round it up. FLOOR(your number) will round it down

Gil Allen
  • 1,169
  • 14
  • 24
6

Combine round and ceiling to get a proper round up.

select ceiling(round(984.375000), 0)) => 984

while

select round(984.375000, 0) => 984.000000

and

select ceil (984.375000) => 985
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Edwin
  • 61
  • 1
  • 1
5

Ceiling is the command you want to use.

Unlike Round, Ceiling only takes one parameter (the value you wish to round up), therefore if you want to round to a decimal place, you will need to multiply the number by that many decimal places first and divide afterwards.

Example.

I want to round up 1.2345 to 2 decimal places.

CEILING(1.2345*100)/100 AS Cost
markthewizard1234
  • 501
  • 6
  • 22
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

Cshah
  • 5,612
  • 10
  • 33
  • 37
  • What if you just want to round up to the nearest penny? If the tax due is $13.052, I need to round that up to $13.053. I could use ceiling(), but I would have to first multiple by 100, then divide by 100. Seems lame. – datagod Sep 23 '11 at 14:48
2

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);

Dominic Rodger
  • 97,747
  • 36
  • 197
  • 212
a1kmm
  • 1,354
  • 7
  • 13
  • welcome to stackoverflow! Anything indented four spaces is formatted as code, you can use the button with binary digits on it to do this in the editor. Hope you don't mind me fixing your answer to do this! – Paul Dixon Sep 07 '09 at 11:16