How to Use ROUND Function in MySQL: Syntax and Examples

In MySQL, use the ROUND() function to round a number to a specified number of decimal places. The syntax is ROUND(number, decimals), where decimals is optional and defaults to 0 if omitted.

Syntax

The ROUND() function rounds a numeric value to the nearest integer or to a specified number of decimal places.

• number: The numeric value you want to round.
• decimals (optional): The number of decimal places to round to. If omitted, it rounds to the nearest whole number.

sql

ROUND(number, decimals)

Example

This example shows how to round numbers to different decimal places using ROUND(). It demonstrates rounding to zero decimals (nearest integer) and to two decimals.

sql

SELECT ROUND(123.4567) AS RoundedToInteger, ROUND(123.4567, 2) AS RoundedToTwoDecimals;

Output

+------------------+---------------------+
| RoundedToInteger  | RoundedToTwoDecimals |
+------------------+---------------------+
|              123 |               123.46 |
+------------------+---------------------+

Common Pitfalls

Common mistakes when using ROUND() include:

• Omitting the decimals argument and expecting decimal rounding (it defaults to 0, rounding to an integer).
• Using negative values for decimals which rounds to the left of the decimal point (this is allowed but sometimes unexpected).
• Confusing ROUND() with FLOOR() or CEIL() which always round down or up respectively.

sql

/* Wrong: expecting two decimals but decimals argument omitted */
SELECT ROUND(123.4567) AS WrongRounding;

/* Right: specify decimals to get two decimal places */
SELECT ROUND(123.4567, 2) AS CorrectRounding;

Output

+----------------+
| WrongRounding  |
+----------------+
|            123 |
+----------------+

+------------------+
| CorrectRounding  |
+------------------+
|           123.46 |
+------------------+

Quick Reference

Usage	Description	Example
ROUND(number)	Rounds to nearest integer	ROUND(12.7) → 13
ROUND(number, decimals)	Rounds to specified decimals	ROUND(12.3456, 2) → 12.35
ROUND(number, 0)	Rounds to nearest integer (same as no decimals)	ROUND(12.3, 0) → 12
ROUND(number, -1)	Rounds to tens place	ROUND(123, -1) → 120

Key Takeaways

Use ROUND(number, decimals) to round numbers to a specific decimal place in MySQL.

If decimals is omitted, ROUND() rounds to the nearest whole number.

Negative decimals round to places left of the decimal point (e.g., tens, hundreds).

ROUND() differs from FLOOR() and CEIL() which always round down or up.

Always specify decimals if you want to keep decimal places.