MariaDB ROUND() vs FLOOR()

Posted on by Ian

MariaDB has a ROUND() function and a FLOOR() function that are similar in some ways, but quite different in other ways.

Below is a quick rundown on the difference between ROUND() and FLOOR() in MariaDB.

The Difference

Here’s the difference between each function in a nutshell:

  • ROUND() rounds its argument to a specified number of decimal places.
  • FLOOR() returns the largest integer value not greater than its argument.

Syntax

Here are the syntaxes and definitions of each function.

ROUND()

The ROUND() function can be used in the following two ways:

ROUND(X)
ROUND(X,D)

ROUND() rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X.

FLOOR()

The FLOOR() function can be used with only one syntax:

FLOOR(X)

FLOOR() returns the largest integer value not greater than X.

Example of the Difference

Here’s a comparison to demonstrate the difference between ROUND() and FLOOR():

SELECT 
    FLOOR(3.6789),
    ROUND(3.6789);

Result:

+---------------+---------------+
| FLOOR(3.6789) | ROUND(3.6789) |
+---------------+---------------+
|             3 |             4 |
+---------------+---------------+

In this case, the ROUND() function rounded the number up, because the next digit (8) is greater than 5.

The FLOOR() function on the other hand, simply returned the largest integer value not greater than the argument (3.6789).

Second Argument

Another difference is that ROUND() accepts an optional second argument, whereas FLOOR() does not.

The second argument allows you to specify how many decimal places to round the number to.

Example

SELECT 
    FLOOR(3.6789),
    ROUND(3.6789, 2);

Result:

+---------------+------------------+
| FLOOR(3.6789) | ROUND(3.6789, 2) |
+---------------+------------------+
|             3 |             3.68 |
+---------------+------------------+

Example of the Same Result

These functions can sometimes return the same result. It all depends on the value of the arguments being passed.

For example, if we place a negative sign in front of the values, we get the same result:

SELECT 
    FLOOR(-3.6789),
    ROUND(-3.6789);

Result:

+----------------+----------------+
| FLOOR(-3.6789) | ROUND(-3.6789) |
+----------------+----------------+
|             -4 |             -4 |
+----------------+----------------+

That’s not to say that a negative value always returns the same result – it doesn’t. It all depends on the actual values being provided and the rounding to take place by ROUND().

Here’s an example where negative values produce different results:

SELECT 
    FLOOR(-3.3739),
    ROUND(-3.3739);

Result:

+----------------+----------------+
| FLOOR(-3.3739) | ROUND(-3.3739) |
+----------------+----------------+
|             -4 |             -3 |
+----------------+----------------+

And here’s an example where positive values return the same result:

SELECT 
    FLOOR(3.3739),
    ROUND(3.3739);

Result:

+---------------+---------------+
| FLOOR(3.3739) | ROUND(3.3739) |
+---------------+---------------+
|             3 |             3 |
+---------------+---------------+

The TRUNCATE() Function

Both functions are different to the TRUNCATE() function, which simply truncates the value to a given number of decimal places.