Rounding Numbers

MySQL gives us four functions to round numbers: CEILING, FLOOR, ROUND, and TRUNCATE. These four functions all have different purposes, but together, they can round a number any way you would like. You might already know the definition of these words from your math classes, but you most likely have no idea what it means to truncate. I will discuss them all in detail so that you can take these concepts with you into other languages, as each language has similar functions. We will not need any tables or additional work as MySQL allows us to input integers into our functions.

The creators long ago thought of rounding numbers in a metaphorical way. The ceiling would be the highest of the room and the floor would be the lowest of the room. Why they did not use up and down is beyond my knowledge, but just know that the ceiling is up and the floor is down like you would expect.

Rounding numbers with MySQL

SQL CEILING Function

SELECT CEILING( 1 ); /* returns 1 */
SELECT CEILING( 1.6 ); /* returns 2 */
SELECT CEILING( 1.4 ); /* returns 2 */

The CEILING function rounds the decimal up to the next integer. The decimal is irrelevant in this function as it is always eliminated when the number rounds up to an integer. Of course, this means that you will lose your decimal places.

SQL FLOOR Function

SELECT FLOOR( 1 ); /* returns 1 */
SELECT FLOOR( 2.1 ); /* returns 2 */
SELECT FLOOR( 2.9 ); /* returns 2 */

If CEILING is up, then the FLOOR function is the same but only the number is rounded down. Again, you will lose the decimal place and an integer will be displayed. The decimals are essentially truncated from the number. Truncated simply means to be cut off.

SQL ROUND Function

SELECT ROUND( 1 ); /* returns 1 */
SELECT ROUND( 1.5 ); /* returns 2 */
SELECT ROUND( 1.4635, 1 ); /* returns 1.5 */

The ROUND function you probably already understand from your math classes. You might have known CEILING and FLOOR, but ROUND is by far the most common. Rounding just means to round up from 5 or down from anything less. ROUND is unique because you can tell SQL which position you would like rounded. In our third statement, we have SQL round to the first decimal by placing 1 as our second argument to the ROUND function.

SQL TRUNCATE Function

SELECT TRUNCATE( 1, 0 ); /* returns 1 */
SELECT TRUNCATE( 1.699, 1 ); /* returns 1.6 */
SELECT TRUNCATE( 185, -1 ); /* returns 180 */

The TRUNCATE function is not very common to use. Basically, it strips characters from your number. Why would you want to lose information about your number? There are a few reasons, but you will find those later in your programming career, but we should talk about why it works. You provide the value in the first argument and the position you want to truncate to in the second argument. You can see in the example that our third SQL statement has a negative one. It returns 180 because we truncated the 5, but we put the placeholder 0 in there so the numbers in the integer keep their position.