SQL ROUND: Rounds a Number to a Specific Precision

Summary: in this tutorial, you will learn how to use the SQL ROUND function to round a number to a specific precision.

Introduction to the SQL ROUND function

The ROUND function is a math function that rounds a number to a specific length or precision. The following illustrates the syntax of the ROUND function.

ROUND(numeric_expression,precision);Code language: SQL (Structured Query Language) (sql)

The ROUND function accepts two arguments.

numeric_expression is a numeric expression that evaluates to a number.
precision can only take a positive or negative integer.

The following statement rounds a number to one decimal place.

SELECT ROUND(100.19,1);Code language: SQL (Structured Query Language) (sql)

 round
-------
 100.2
(1 row)Code language: SQL (Structured Query Language) (sql)

Because the precision is 1, the ROUND function rounds a number up to the nearest decimal. See the following example.

SELECT ROUND(100.14,1);Code language: SQL (Structured Query Language) (sql)

 round
-------
 100.1
(1 row)Code language: SQL (Structured Query Language) (sql)

If the precision is a negative integer, the ROUND function will round numbers on the left side of the decimal point, for example:

SELECT ROUND(109.14,-1);Code language: SQL (Structured Query Language) (sql)

 round
-------
   110
(1 row)Code language: SQL (Structured Query Language) (sql)

In the above statement, the ROUND function rounded up the last digit of the number on the left of the decimal point. In addition, the numbers on the right side of the decimal point went to zero.

Some database systems such as Microsoft SQL Sever, IBM DB2, Sybase ASE display the zero (.00) after the decimal point of the number while the other e.g., Oracle database, PostgreSQL, MySQL do not.

Note that Oracle, PostgreSQL, and MySQL has a version of the ROUND function that accepts a single argument. If you pass a single argument, the ROUND function rounds the number up the nearest integer. For example, the following statement returns 110, which is the nearest integer.

SELECT ROUND(109.59);Code language: SQL (Structured Query Language) (sql)

 round
-------
   110
(1 row)Code language: SQL (Structured Query Language) (sql)

SQL ROUND example

See the following employees and departments tables in the sample database.

The following statement uses the ROUND function to round the average salary of employees in each department to the nearest integers.

SELECT 
    department_name,
    ROUND(AVG(salary), 0) average_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY e.department_id
ORDER BY department_name;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the ROUND function to round a number to a specific precision.

Was this tutorial helpful ?