Monday, November 18, 2013

MySQL: rounding numbers

1. ROUND(x,d)
x: value to round
d: decimal number
For example:
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 */

2. CEILING(x)
CEILING = ROUND x up to the nearest integer.
For example:
SELECT CEILING( 1 );   /* = 1 */
SELECT CEILING( 1.4 ); /* = 2 */
SELECT CEILING( 1.6 ); /* = 2 */
SELECT CEILING( -1.4 ); /* = -1 */
SELECT CEILING( -1.6 ); /* = -1 */

3. FLOOR(x)
FLOOR =  ROUND x down to the nearest integer.
For example:
SELECT FLOOR( 1 );   /* = 1 */
SELECT FLOOR( 1.4 ); /* = 1 */
SELECT FLOOR( 1.6 ); /* = 1 */
SELECT FLOOR( -1.4 ); /* = -2 */
SELECT FLOOR( -1.6 ); /* = -2 */

4. TRUNCATE(x,y)
x: value to truncate
y: decimal number after truncated. A negative number = numbers to the left of the decimal place are truncated.
For example:
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 */
Subscribe to RSS Feed Follow me on Twitter!