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:
sql
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
sql
SELECT ROUND(123.4567, 2); -- Output: 123.46
This command rounds the number to two decimal places.
You can also use ROUND with columns:
sql
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.
sql
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.
sql
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.
sql
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
sql
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:
sql
SELECT FLOOR(9.99); -- Output: 9
Use this when you want to always round down.
Round Up in SQL
To always round up, use:
sql
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
sql
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
sql
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:
sql
ROUND(number, decimal_places, operation)
- If
operation = 0(or omitted), SQL rounds normally. - If
operation ≠ 0, it truncates instead of rounding.
Example:
sql
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.
sql
SELECT ROUND(NULL, 2); -- Output: NULL
Always handle potential NULLs with functions like COALESCE():
sql
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.
sql
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.
sql
-- 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:
sql
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?
sql
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
-- 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.
sql
-- 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.