In MariaDB, ROUND() is a built-in numeric function that rounds its argument to a given number of decimal places.

Syntax

The function can be used in the following two ways:

ROUND(X)
ROUND(X,D)

Where X is the value to round, and D is an optional argument that specifies the number of decimal places to round it to.

Example

Here’s an example:

SELECT ROUND(7.5);

Result:

+------------+
| ROUND(7.5) |
+------------+
|          8 |
+------------+

Passing a negative value produces the following result:

SELECT ROUND(-7.5);

Result:

+-------------+
| ROUND(-7.5) |
+-------------+
|          -8 |
+-------------+

Specify Decimal Places

By default, the ROUND() function rounds the value to zero decimal places. We can specify the number of decimal places to round the value to by passing a second argument.

Example:

SELECT ROUND(7.5164, 2);

Result:

+------------------+
| ROUND(7.5164, 2) |
+------------------+
|             7.52 |
+------------------+

Negative Decimal Places

The second argument can be a negative value if required. Passing a negative value causes digits to the left of the decimal place to become zero.

Example:

SELECT ROUND(3724.51, -2);

Result:

+--------------------+
| ROUND(3724.51, -2) |
+--------------------+
|               3700 |
+--------------------+

Compared to FLOOR()

The ROUND() function is different to the FLOOR() function. The ROUND() function rounds the number up in some instances and down in others. The FLOOR() function, on the other hand, always returns the largest integer value not greater than its argument.

Here’s a comparison to demonstrate this difference:

SELECT 
    FLOOR(3.6789),
    ROUND(3.6789);

Result:

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

Also, FLOOR() doesn’t accept a second argument like ROUND() does.

ROUND() is also different to the TRUNCATE() function, which simply truncates the number to a given decimal place, without any rounding.

Non-Numeric Arguments

Here’s what happens when we provide a non-numeric argument:

SELECT ROUND('Ten');

Result:

+--------------+
| ROUND('Ten') |
+--------------+
|            0 |
+--------------+
1 row in set, 1 warning (0.028 sec)

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Ten' |
+---------+------+-----------------------------------------+

Missing Arguments

Calling ROUND() without an argument results in an error:

SELECT ROUND();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'ROUND'