SQL ROUND Function: Syntax, Usage, and Examples

The SQL ROUND function lets you control how many decimal places a number shows in query results. Use this function to clean up output, match business rules, or prepare values for reports and calculations.

Whether you're rounding prices, percentages, or averages, the SQL ROUND function gives you the precision you need.

How to Use the SQL ROUND Function

The basic syntax looks like this:

ROUND(number, decimal_places)

• number: The numeric value you want to round.
• decimal_places: The number of digits to keep after the decimal point.

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

Example

SELECT ROUND(123.4567, 2);  -- Output: 123.46

This command rounds the number to two decimal places.

You can also use ROUND with columns:

SELECT ROUND(price, 0) AS rounded_price
FROM products;

This example rounds prices to the nearest whole number.

When to Use the SQL ROUND Function

Clean Up Output for Reports

If you're building a dashboard or client-facing report, you can make numbers easier to read with the SQL ROUND function.

SELECT ROUND(revenue, 1) AS short_revenue
FROM sales_data;

A one-digit decimal often looks cleaner than long floats.

Control Precision in Aggregates

When averaging or calculating percentages, ROUND keeps results from getting messy.

SELECT ROUND(AVG(score), 2) AS average_score
FROM exam_results;

You set the level of precision your users expect.

Standardize Calculations Across Systems

Use SQL ROUND to prevent small differences between systems. If you compare values from different databases or export results to other apps, consistent rounding reduces errors.

SELECT ROUND(amount * tax_rate, 2) AS tax_total
FROM invoices;

Here, rounding keeps your tax values consistent across platforms.

Examples of ROUND in SQL

Round to Whole Numbers

SELECT ROUND(99.9);  -- Output: 100

Without a second argument, SQL rounds to the nearest integer.

Round Down in SQL

The ROUND function doesn't always round down, but you can force it with other techniques if needed. For example:

SELECT FLOOR(9.99);  -- Output: 9

Use this when you want to always round down.

Round Up in SQL

To always round up, use:

SELECT CEILING(9.01);  -- Output: 10

While ROUND decides based on standard rounding rules, FLOOR and CEILING give you control over rounding direction.

Round Negative Numbers

SELECT ROUND(-123.456, 1);  -- Output: -123.5

Negative values round just like positive ones. The sign stays the same.

SQL ROUND with Currency Values

SELECT ROUND(price * 1.20, 2) AS price_with_tax
FROM products;

This query adds 20% tax to each price and rounds to two decimal places—perfect for displaying currency.

Learn More About ROUND in SQL

SQL Server ROUND Behavior

In SQL Server, the ROUND() function behaves similarly, but it includes a third optional argument:

ROUND(number, decimal_places, operation)

• If operation = 0 (or omitted), SQL rounds normally.
• If operation ≠ 0, it truncates instead of rounding.

Example:

SELECT ROUND(123.456, 2, 1);  -- Output: 123.45

This truncates the value rather than rounding it.

SQL Query ROUND and NULL Values

ROUND returns NULL if the input value is NULL.

SELECT ROUND(NULL, 2);  -- Output: NULL

Always handle potential NULLs with functions like COALESCE():

SELECT ROUND(COALESCE(score, 0), 1) FROM results;

This approach prevents rounding errors from missing data.

ROUND in SQL vs. CAST

Both ROUND() and CAST() can change how a number looks, but they behave differently.

SELECT CAST(123.456 AS DECIMAL(5,2));  -- Output: 123.46
SELECT ROUND(123.456, 2);              -- Output: 123.46

The CAST method changes the data type, while ROUND simply adjusts the output. Choose the right one based on context.

Rounding in SQL vs. Truncating

Rounding rounds to the nearest value. Truncation simply drops digits.

-- ROUND
SELECT ROUND(3.6789, 2);  -- Output: 3.68

-- TRUNCATE in MySQL
SELECT TRUNCATE(3.6789, 2);  -- Output: 3.67

Different SQL dialects support different truncation functions.

SQL Round Command With GROUP BY

ROUND works well with aggregate queries. Here's how to combine it with GROUP BY:

SELECT department, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department;

You get clean, whole-number averages per department.

SQL Round Down With Multiples

Need to round down to the nearest 10 or 100?

SELECT FLOOR(price / 10) * 10 AS rounded_down
FROM items;

This expression rounds numbers down to the nearest multiple of 10.

To round up, use CEILING() instead of FLOOR().

SQL ROUND vs. FORMAT (SQL Server / MySQL)

In some SQL engines, the FORMAT() function can also round and format numbers:

-- SQL Server
SELECT FORMAT(1234.5678, 'N2');  -- Output: '1,234.57'

But FORMAT() returns a string, not a number. If you need to keep the value numeric, stick with ROUND.

How to Round in SQL for Reporting

Most reporting tools like Tableau, Power BI, and Excel round values visually. But it’s best to round your SQL query first to avoid surprises.

-- Round value for export
SELECT ROUND(total_cost, 2) AS total_cost_cleaned
FROM project_budget;

This gives the report clean numbers and avoids rounding inconsistencies between platforms.

You can round in SQL using either ROUND(), FLOOR(), or CEILING(), depending on whether you want standard rounding, forced rounding down, or forced rounding up. With the SQL round function, you gain full control over the display and precision of numerical results.