Home » Tutorials » SQL » Rounding Numbers

# 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

Example
```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

Example
```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

Example
```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

Example
```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.

• Stewart, Suzy. "Rounding Numbers". After Hours Programming. Accessed on August 19, 2022. https://www.afterhoursprogramming.com/tutorial/sql/rounding-numbers/.

• Stewart, Suzy. "Rounding Numbers". After Hours Programming, https://www.afterhoursprogramming.com/tutorial/sql/rounding-numbers/. Accessed 19 August, 2022.

### 2 thoughts on “Rounding Numbers”

1. my question is :
SELECT ROUND(15.193,1)
my ans is in sql : 15.100

but i want to 15.1

how am i solved in sql

• SELECT TRUNCATE(15.193, 1);
+———————+
| TRUNCATE(15.193, 1) |
+———————+
| 15.1 |
+———————+
1 row in set (0.00 sec)

This site uses Akismet to reduce spam. Learn how your comment data is processed.