Problem
You want to round up to the nearest integer.
Background
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.
Discussion
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
CEILING(FinalOrderPrice)
to
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.
Leave a Reply