SQL ROUND Function

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.

Here’s the syntax of the ROUND function:

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

The ROUND function accepts two arguments.

1. numeric_expression is a numeric expression that evaluates to a number.
2. precision can be an integer (0, negative, and positive).

Basic SQL ROUND function examples #

The following statement uses the ROUND function to round a 100.19 to one decimal place.

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

Try it

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

Since the precision is 1, the ROUND function rounds a number up to the nearest decimal.

The following example uses the ROUND function to round 100.14 to one decimal place:

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

Try it

 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)

Try it

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

In this example, the ROUND function rounds up the last digit of the number on the left of the decimal point. Additionally, the numbers on the right side of the decimal point becomes 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)

Try it

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

Using SQL ROUND function with table data #

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
  department_name
ORDER BY
  department_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name  | average_salary
------------------+----------------
 Accounting       |          10150
 Administration   |           4400
 Executive        |          19333
 Finance          |           8600
 Human Resources  |           6500
 IT               |           5760
 Marketing        |           9500
 Public Relations |          10000
 Purchasing       |           4150
 Sales            |           9617
 Shipping         |           5886Code language: PHP (php)

Summary #

• Use the ROUND function to round a number to a specific precision.