How to Round Up to Nearest Integer

·

·

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.

Rounded to Nearest Integer In SQL

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.

One response to “How to Round Up to Nearest Integer”
  1. 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”!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog


MySQL PostgreSQL SQLite SqlServer