# 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.date | seller | item | Quantity (in units or kilos) | unit_price | total |
---|---|---|---|---|---|

2021-12-06 | Charles | Soda Bbg 500Ml | 2 | 3.23 | 6.46 |

2021-12-06 | Charles | Ice Cream 10Oz | 2 | 2.76 | 5.52 |

2021-12-06 | Charles | Egg Pack HpyHen | 1.2 | 0.75 | 0.9375 |

2021-12-06 | Charles | Banana Ecuador | 1.87 | 2.54 | 4.7498 |

2021-12-07 | Charles | Shampoo A100 | 4 | 0.99 | 3.96 |

2021-12-07 | Charles | Batteries A3 | 10 | 1.53 | 15.30 |

2021-12-07 | Mary | Candy Pack B20 | 1 | 2.15 | 2.15 |

2021-12-07 | Mary | Banana Ecuador | 1.47 | 2.54 | 3.7338 |

2021-12-07 | Mary | Ice Cream 10Oz | 1 | 2.76 | 2.76 |

2021-12-07 | Mary | Soda Bbg 500Ml | 1 | 3.23 | 3.23 |

2021-12-08 | Charles | Egg Pack HpyHen | 1.43 | 0.75 | 1.0725 |

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:

date | seller | item | quantity | unit_price | total |
---|---|---|---|---|---|

2021-12-06 | Charles | Soda Bbg 500Ml | 2 | 3.23 | 6.46 |

2021-12-06 | Charles | Ice Cream 10Oz | 2 | 2.76 | 5.52 |

2021-12-06 | Charles | Egg Pack HpyHen | 1.2 | 0.75 | 0.94 |

2021-12-06 | Charles | Banana Ecuador | 1.87 | 2.54 | 4.75 |

2021-12-07 | Charles | Shampoo A100 | 4 | 0.99 | 3.96 |

2021-12-07 | Charles | Batteries A3 | 10 | 1.53 | 15.30 |

2021-12-07 | Mary | Candy Pack B20 | 1 | 2.15 | 2.15 |

2021-12-07 | Mary | Banana Ecuador | 1.47 | 2.54 | 3.73 |

2021-12-07 | Mary | Ice Cream 10Oz | 1 | 2.76 | 2.76 |

2021-12-07 | Mary | Soda Bbg 500Ml | 1 | 3.23 | 3.23 |

2021-12-08 | Charles | Egg Pack HpyHen | 1.43 | 0.75 | 1.07 |

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.

seller | total_revenue |
---|---|

Charles | 12 |

Mary | 38 |

## 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:

Value | Round(value,1) |
---|---|

1.20 | 1.2 |

1.22 | 1.2 |

1.25 | 1.3 |

1.27 | 1.3 |

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).

BINARY_FLOAT_VALUE | ROUND(BINARY_FLOAT_VALUE) | NUMERIC_VALUE | ROUND(NUMERIC_VALUE) |
---|---|---|---|

23.5 | 24 | 23.5 | 24 |

24.5 | 24 | 24.5 | 25 |

## 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:

Value | Round(value, -2) |
---|---|

12345 | 12300 |

12355 | 12400 |

12350 | 12400 |

101 | 100 |

199 | 200 |

99 | 100 |

## 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:

item | unit_price | decimal_part |
---|---|---|

Candy Pack B20 | 2.15 | 0.15 |

Soda Bbg 500Ml | 3.23 | 0.23 |

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!