How to Round in SQL

Use ROUND() when numeric values should display with a fixed number of decimal places or when calculations need cleaner precision. This is especially useful for currency, averages, percentages, and reporting dashboards.

What you’ll build or solve

You’ll learn how to round in SQL using ROUND() with optional decimal precision. You’ll also know how rounding differs from truncation.

When this approach works best

This approach is the right choice when decimal output should be easier to read or business-safe for reporting.

Common real-world scenarios include:

• Currency totals
• Average ratings
• Tax calculations
• Percentage dashboards
• Scientific measurements

This is a bad idea when you need to cut off decimals without mathematical rounding. In that case, use TRUNC() or equivalent database-specific functions.

Prerequisites

You only need:

• A SQL table with numeric columns
• Basic SELECT and aggregate knowledge

Step-by-step instructions

Step 1: Wrap the numeric expression in ROUND()

The common pattern is:

ROUND(value, decimal_places)

Example:

SELECT ROUND(price, 2)
FROM products;

This rounds to 2 decimal places.

If you omit the second argument, SQL rounds to the nearest whole number.

SELECT ROUND(rating)
FROM reviews;

This is especially useful for aggregate output.

SELECT ROUND(AVG(total), 2)
FROM orders;

What to look for:

• Second argument controls decimal places
• Omit it for whole numbers
• Great for currency and averages
• Works on expressions too
• Different databases may vary on midpoint rules

Examples you can copy

Currency

SELECT ROUND(total, 2)
FROM invoices;

Rounded average

SELECT ROUND(AVG(score), 1)
FROM exams;

Percentage

SELECT ROUND(conversion_rate * 100, 2)
FROM campaigns;

Common mistakes and how to fix them

Mistake 1: Expecting truncation instead of rounding

What the reader might do:

SELECT ROUND(4.99)

Why it breaks: this returns 5, not 4.

Corrected approach:

Use truncation helpers if decimals should simply be cut off.

Mistake 2: Rounding too early in calculations

What the reader might do:

SELECT ROUND(price, 2) * quantity

Why it breaks: repeated rounding can compound calculation error.

Corrected approach:

Round only the final expression.

SELECT ROUND(price * quantity, 2)

Mistake 3: Using string columns

What the reader might do:

SELECT ROUND(price_text, 2)

Why it breaks: text values cannot be rounded reliably.

Corrected approach:

Convert to numeric first if needed.

Troubleshooting

If the result rounds unexpectedly, confirm the decimal precision argument.

If calculations drift, round only the final result.

If the output is text, cast it to numeric.

If midpoint rounding differs, check database-specific rules.

Quick recap

• Use ROUND() for readable numeric precision
• Add decimal places as the second argument
• Great for currency and averages
• Round final expressions, not intermediate steps
• Use truncation when cut-off behavior is required