Rounding numbers with MySQL

MySQL has a few great ways to round numbers and each function is good for a specific purpose. CEILING, FLOOR, ROUND, and TRUNCATE are the functions that we will be using in this tutorial. We do not need to create tables for this tutorial because we will just place the numbers in our MySQL statements. Let's get started.

CEILING()

SELECT CEILING( 1 );   /* returns 1 */

SELECT CEILING( 1.8 ); /* returns 2 */

SELECT CEILING( 2.4 ); /* returns 3 */

The CEILING function rounds the decimal up to the next integer. It does not matter if you have a .3 or a .8, both of those numbers with round up to the next integer.

FLOOR()

SELECT FLOOR( 1 );   /* returns 1 */

SELECT FLOOR( 1.2 ); /* returns 1 */

SELECT FLOOR( 1.9 ); /* returns 1 */

The FLOOR function is exactly the opposite of the CEILING function. Any decimal will be erased and the initial integer value will be returned. The decimals are essentially truncated from the number.

ROUND()

SELECT ROUND( 1 );   /* returns 1 */

SELECT ROUND( 2.5 ); /* returns 3 */

SELECT ROUND( 1.7676, 1 ); /* returns 1.8 */

The ROUND function does what you would expect, it rounds numbers. It is unique in the fact that you can tell it which number to round. In the third statement of the previous example, we tell it to round to the first decimal. So, it rounds the "7676" to an 8. All of the other mathematical rules of rounding apply.

TRUNCATE()

SELECT TRUNCATE( 1, 0 );       /* returns 1    */

SELECT TRUNCATE( 1.5999, 1 );  /* returns 1.5  */

SELECT TRUNCATE( 175, -1 );  /* returns 170 */

The TRUNCATE function is probably the least used as we typically do not want to truncate data. However, if you do for some reason want to cut part of the string off, you can provide the value and the number of decimals you want to remain. It should be noted that TRUNCATE can do negative numbers to cut off numbers left of the zero. However, a placeholder of 0 will take its position so the number keeps its rough value.