Rounding numbers with MySQLRounding numbers with MySQL

Posted May 20th, 2009 in MySql

MySQL has a number of ways of rounding numbers with the CEILING() FLOOR() ROUND() and TRUNCATE() functions.

ROUND()

ROUND() takes two arguments. The first is the number to round and the second optional argument the number of decimal places to round the number to. If the second argument is not specified then it defaults to 0 thus rounding to the nearest integer. If the part to be rounded is 5 or higher, the number is rounded up otherwise it is rounded down.

Here are some examples, with the result after the SELECT part in a comment:

SELECT ROUND( 1 );   /* = 1 */
SELECT ROUND( 1.4 ); /* = 1 */
SELECT ROUND( 1.5 ); /* = 2 */

SELECT ROUND( -1.4 ); /* = -1 */
SELECT ROUND( -1.5 ); /* = -2 */

SELECT ROUND( 1.4212, 1 ); /* = 1.4 */
SELECT ROUND( 1.4512, 1 ); /* = 1.5 */

CEILING()

CEILING() always rounds the number up to the nearest integer. There is no argument for precision so it always returns an integer. CEIL() is an alias for CEILING() so can also be used as the function name. Here are some examples:

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

SELECT CEILING( -1.4 ); /* = -1 */
SELECT CEILING( -1.6 ); /* = -1 */

FLOOR()

FLOOR() works in the same way as CEILING() but always rounds the number down and also has no argument for the precision. Here are some examples:

SELECT FLOOR( 1 );   /* = 1 */
SELECT FLOOR( 1.4 ); /* = 1 */
SELECT FLOOR( 1.6 ); /* = 1 */

SELECT FLOOR( -1.4 ); /* = -2 */
SELECT FLOOR( -1.6 ); /* = -2 */

TRUNCATE()

TRUNCATE() takes two arguments: the number to be truncated and the number of decimal places to truncate to. The second argument is required and MySQL will produce an error message if it is not specified.

The numbers after the specified number of decimal places are truncated from the result. If the decimal places is a negative number then the numbers to the left of the decimal place are truncated.

Some examples:

SELECT TRUNCATE( 1, 0 );       /* = 1    */
SELECT TRUNCATE( 1.5555, 0 );  /* = 1    */
SELECT TRUNCATE( 1.5555, 1 );  /* = 1.5  */
SELECT TRUNCATE( -1.5555, 0 ); /* = -1   */
SELECT TRUNCATE( -1.5555, 1 ); /* = -1.5 */

SELECT TRUNCATE( 12345, -1 );  /* = 12340 */
SELECT TRUNCATE( 12345, -2 );  /* = 12300 */
SELECT TRUNCATE( 12345, -3 );  /* = 12000 */

A final note

Although the examples here show constants in the function calls you would obviously normally be selecting a field with the function call. I've used constants in the examples to show what the result of the function call would be. For example, you'd be more likely to do something like this: "SELECT ..., ROUND(some_field, 2), ... FROM ..."

Related posts: