How to Use the ROUND() Function in SQL

Need to round numeric results in SQL? The ROUND() function is here to do the job.

Modern relational databases offer many built-in functions that extend the capabilities of SQL. In this article, we will use practical examples to explain how the ROUND() function works. (Spoiler alert: If you guessed that it rounds a number to a certain decimal place, you’re right! Read on to find out more.)

SQL Standard Functions

Each SQL database (PostgreSQL, MySQL, Oracle, SQL Server among others) has a set of built-in functions. To understand what this means, let’s talk about functions.

A function is a predefined piece of code that transforms its arguments into a result. For example, the SUBSTRING() function receives a character string (i.e. a word, sentence, etc.) as an argument and returns part of this string. Other functions like MAX() (which returns the largest value) and MIN() (which returns the smallest value) process numerical data; you can learn more about these functions in the article What Are the SQL MIN() and MAX() Functions?

A function can be called in a SQL query whenever you need to provide a value. You can call a function in the SELECT list of columns, in the WHERE condition, or in any other place of the query where a value must be provided. There are a lot of functions in SQL, and every SQL database provides a different set of functions.

If you’re looking for a comprehensive course about functions in SQL, check out our interactive course on Standard SQL Functions. It covers both basic and advanced SQL functions, equipping you with everything you need to start using them to process numerical, text, and other types of data in SQL databases.

What Is the SQL ROUND() Function?

The ROUND function in SQL is used to round a given number to the nearest integer or to a certain decimal place. We will show several example queries using the ROUND() function, but first we will introduce a sample SQL table called sales. This table records sales data for a small marketplace. Some of the products (such as eggs or vegetables) are sold based on weight; in this case, the quantity column records the number of kilos sold, which can include decimal digits.

Let’s start by showing a simple query using the ROUND() function in Standard SQL. If we look at the column total in the sales table above, we can see some records where the value has 3 decimal digits. Suppose we want a report showing all the sales, but we only want a maximum of 2 decimal digits in the total column. This is the query we’d use:

SELECT 
  date, 
  seller, 
  item, 
  quantity, 
  unit_price, 
  ROUND(total, 2) AS total
FROM sales

We can see that the ROUND() function receives two parameters. The first one is the numeric value to be rounded (i.e. the total column); the second one is called precision and indicates the number of decimal digits we want to maintain in the rounded number. The result of this query is shown below:

In the total column above, we can see the result of the ROUND() function. Note that all the total values have two decimal digits. The value was rounded to the nearest hundredth, meaning that the ROUND() transformation is not a simple truncation. For example 4.7498 was rounded to 4.75 which is a higher value; 3.7338 was rounded to 3.73, which is a lower value.

As a general rule, numbers with a 5 or more in the specified decimal place are rounded up (4.7498 is rounded up to 4.75) and numbers under 5 are rounded down (3.7338 is rounded down to 3.73).

Using the ROUND() Function With One Argument

The ROUND() function we used in the previous example had two arguments: the value to round, and the precision (which was 2). The precision indicates the number of decimal places we want in the result. However, you can omit the precision parameter, in which case ROUND() assumes a default precision of 0 and returns integer values with 0 decimal places.

Let’s see an example. Suppose we want to obtain our market’s total revenue grouped by seller. However, we want a compact and simple report, with the revenue values as integers. This is the code we’d use in a MySQL database:

SELECT
  seller, 
  ROUND(SUM(total)) as total_revenue
FROM sales
GROUP BY seller

We can see the results below.

Using SQL ROUND() Function in Different Databases

In the next example, we want to show the result of the ROUND() function on some specific values, like values ending in 0.5 or 0.0. For that purpose, we will show a query that demonstrates how rounding works:

SELECT 1.20 as Value, ROUND(1.20,1) AS "Round(value,1)" 
UNION
SELECT 1.22 as Value, ROUND(1.22,1) AS "Round(value,1)" 
UNION
SELECT 1.25 as Value, ROUND(1.25,1) AS "Round(value,1)" 
UNION
SELECT 1.27 as Value, ROUND(1.27,1) AS "Round(value,1)" 

The previous queries use only constants (i.e. explicitly defined values) instead of data from a table. This is why there’s no FROM clause in the query. The results are:

Regarding the results of the ROUND() function, we can conclude that for values in the range 1.20 to 1.249999, the result of the ROUND() is 1.2, however for values in the range 1.25 to 1.299999 the result is 1.3. Perhaps the most important point here is that 1.25 is rounded to 1.3.

The ROUND() function in Oracle is similar to the ROUND() function in other databases. However for the data type BINARY_FLOAT, Oracle implements a variation in the rounding algorithm called round half to even. This rounds numbers with a fractional part of 0.5 to the nearest even integer. Thus, ROUND(23.5) returns 24 and ROUND(24.5) returns 24 too. As we previously mentioned, this behavior of the ROUND() function is only for the BINARY_FLOAT data type; for other numeric data types, ROUND(24.5) returns 25. We can see this in the following example query executed in Oracle.

SELECT	
  binary_float_value,
  ROUND(binary_float_value), 
  numeric_value,
  ROUND(numeric_value) 
FROM test_round_with_float;

In the results, we can see the difference between the two data types. ROUND(24.5) returns 24 when the data type of the argument is BINARY_FLOAT; it returns 25 when the data type is any other numeric data type (like NUMBER).

Using SQL ROUND() with Negative Precision

ROUND() offers a different functionality when the precision parameter is a negative number. In the example below, we can obtain the nearest multiple of 100 by using ROUND(value, -2). We will use a query similar to the previous one to show this functionality:

SELECT 12345 as Value, ROUND(12345,-2) AS "Round(value,-2)" 
UNION
SELECT 12355 as Value, ROUND(12355,-2) AS "Round(value,-2)" 
UNION
SELECT 12350 as Value, ROUND(12350,-2) AS "Round(value,-2)" 
UNION
SELECT 101 as Value, ROUND(101,-2) AS "Round(value,-2)" 
UNION
SELECT 199 as Value, ROUND(199,-2) AS "Round(value,-2)" 
UNION
SELECT 99 as Value, ROUND(99,-2) AS "Round(value,-2)" 

The current query is executed in MS SQL Server. Like the previous query, we continue using constant data to show how ROUND() works. You can see how a precision parameter of -2 works on different values. The result of ROUND(value, -2) is the nearest multiple of 100 to the value. We can see in the result of the query below:

Using ROUND() in WHERE

In the following example, we will show how to use the SQL ROUND() function in the WHERE clause of a query. Suppose we want a report showing all the items having a price with the decimal part lower than 50 cents. The following condition will identify those prices:

ROUND(unit_price) < unit_price

The idea behind the previous condition is as follows. When the decimal part in the unit_price column is less than 0.50, the ROUND(unit_price) function will return a lower value ( if price is 3.23 then, ROUND(3.23) returns 3.00, which is lower than 3.23). Then entire SQL query is:

SELECT 
  distinct item, 
  unit_price,
  unit_price - ROUND(unit_price) AS decimal_part
FROM sales
WHERE ROUND(unit_price) < unit_price

We also added an extra field called decimal_part in the SELECT list to show how to calculate the decimal part of a number using the ROUND() function. The previous query was executed in a PostgreSQL database; we can see the results below:

If you’d like more info on the SUM() function, I suggest reading the article SQL SUM() Function Explained with 5 Practical Examples.

Use SQL Functions to Power Your Queries

In this article, we explained the ROUND() function in SQL using examples from different databases. No matter which database you prefer, you now know how ROUND() works and how you can use it in your queries and reports.

Before ending, I have another recommendation for you. Our free Standard SQL Functions Cheat Sheet lets you quickly find details about SQL’s built-in functions, aggregate functions, and more. I use it almost every day when working with SQL. And don’t forget about our Standard SQL Functions, where you can learn and practice numeric, text, and date functions. Increase your skills, increase your assets!