You want to round up to the nearest integer.
Suppose you have a list of final sales amount that you want to round to make your sales analysis easier. For this example, we’ll use data from the PizzaDB sample database. Here is the query you can use with MS SQL, PostgeSQL, and MySQL to get to the data:
select LastName, OrderDate, FinalOrderPrice from CustomerOrderSummary where OrderDate = '2022-03-01'
What is the easiest way to round these values up to the nearest integer? Meaning, if the price is 14.36, you want to round up to 15.
Solution to Round up to Nearest Integer
To round up, we’ll use the CEILING() built-in function. CEILING() returns the next highest integer. It is the opposite of FLOOR() which returns the lowest integer.
Here how we round up to the next nearest integer:
select LastName, OrderDate, FinalOrderPrice, Ceiling(FinalOrderPrice) RoundedFinalOrderPricefrom CustomerOrderSummary where OrderDate = '2022-03-01'
You can see below how the yellow values are rounded up to the nearest integer.
In general my “go to” function for general “rounding” is ROUND() as you can use it to round to specific decimal places; however, CEILING() is very handy when you need to round up to the nearest int.
It is simple and compact. To see this Compare
Cast(Round(FinalOrderPrice + .5, 0) as int)
The second expression is obvious more complex. As it forces ROUND() to round up, and then ensures the result is cast as an int.
Nice post! Seems like CEILING and FLOOR are great when you can’t really have a “fraction” of something. For example saying “17.464 people”. You can’t really have a fraction of a person, so you really just ought to round that up to “18”!