Archives

The List

« MySQL Function of the Day: Week Six | Main | TRUNCATE :: MySQL Function of the Day »

ROUND() :: MySQL Function of the Day

By Carsten | April 21, 2008

Function name: ROUND
Aliases: -
Function type: Numerical function
Purpose: Round a number to a certain number of decimal places

Description: MySQLs ROUND() is an extremely versatile implementation of rounding functions wrapped into one single function. To wit:

round(123.456): 123 # as you would expect
round(123.456, 0): 123 # 2nd argument defaults to 0 anyway
round(123.456, 2): 123.46 # 2nd argument indicates number of decimals
round(123.456, -1): 120 # 2nd argument can be negative, too, -1 rounds to nearest 10; -2 to hundreds, etc.
round(-123.456): -123 # it all works for negative numbers as well
round(-123.456, 0): -123
round(-123.456, 2): -123.46
round(-123.456, -1): -120

Rounding of numbers that are exactly half-way between two target numbers depends on the data type of the first argument:

If the first argument is an INTEGER or DECIMAL value, the number is rounded up. This includes literal values (e.g. when you type in “123.456″ in mysql> )

If the first argument is a floating-point number, such as a number stored in a FLOAT field in a table, the rounding depends on the C library that was used to compile mysql. Most likely, this means that “banker’s rounding” is used and numbers ending on .5 will be rounded to the nearest even integer.

If ROUND doesn’t quite do what you need, check out one of the several other rounding functions available in MySQL such as TRUNCATE, FLOOR and CEILING.

MySQL manual entry on ROUND()

MySQL Function of the Day is a small series of concise information regarding most of the functions and operators available in MySQL. Inspired by PHPs funcaday, it’s meant to provide a daily dose of something in-between “Aha!” and “Ho-hum, I knew that already”, depending on your level of experience with MySQL. You can access the entire series with your browser here or pick up the RSS feed here.

Topics: MySQL Function of the day |

One Response to “ROUND() :: MySQL Function of the Day”

  1. TRUNCATE :: MySQL Function of the Day | Carsten’s Random Ramblings Says:
    April 22nd, 2008 at 9:05 am

    [...] ROUND() :: MySQL Function of the Day | [...]

Comments