{"id":958,"date":"2016-07-17T02:55:53","date_gmt":"2016-07-17T02:55:53","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=958"},"modified":"2025-03-16T20:13:05","modified_gmt":"2025-03-17T03:13:05","slug":"sql-round","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-math-functions\/sql-round\/","title":{"rendered":"SQL ROUND Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL ROUND<\/code> function to round a number to a specific precision.<\/p>\n\n\n\n

Introduction to the SQL ROUND function #<\/a><\/h2>\n\n\n\n

The ROUND<\/code> function is a math function<\/a> that rounds a number to a specific length or precision. <\/p>\n\n\n\n

Here’s the syntax of the ROUND<\/code> function:<\/p>\n\n\n

ROUND(numeric_expression, precision);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The ROUND<\/code> function accepts two arguments.<\/p>\n\n\n\n
    \n
  1. numeric_expression <\/code>is a numeric expression that evaluates to a number.<\/li>\n\n\n\n
  2. precision <\/code>can be an integer (0, negative, and positive).<\/li>\n<\/ol>\n\n\n\n
    Basic SQL ROUND function examples #<\/a><\/h2>\n\n\n\n
    The following statement uses the ROUND<\/code> function to round a 100.19<\/code> to one decimal place.<\/p>\n\n\n
    SELECT<\/span> ROUND<\/span>(100.19<\/span>,1<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
     round\n-------<\/span>\n 100.2\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Since the precision is 1, the ROUND<\/code> function rounds a number up to the nearest decimal. <\/p>\n\n\n\n
    The following example uses the ROUND<\/code> function to round 100.14<\/code> to one decimal place:<\/p>\n\n\n
    SELECT<\/span>\n  ROUND<\/span>(100.14<\/span>, 1<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
     round\n-------<\/span>\n 100.1\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    If the precision is a negative integer, the ROUND<\/code> function will round numbers on the left side of the decimal point, for example:<\/p>\n\n\n
    SELECT<\/span> ROUND<\/span>(109.14<\/span>,-1<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
     round\n-------<\/span>\n   110\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this example, the ROUND<\/code> 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.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    Note that Oracle, PostgreSQL, and MySQL has a version of the ROUND<\/code> function that accepts a single argument. <\/p>\n\n\n\n
    If you pass a single argument, the ROUND<\/code> function rounds the number up the nearest integer. For example, the following statement returns 110, which is the nearest integer.<\/p>\n\n\n
    SELECT<\/span>\n  ROUND<\/span>(109.59<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
     round\n-------<\/span>\n   110\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Using SQL ROUND function with table data #<\/a><\/h2>\n\n\n\n
    See the following employees<\/code> and departments<\/code> tables in the sample database<\/a>.<\/p>\n\n\n\n
    \"emp_dept_tables\"<\/figure>\n\n\n\n
    The following statement uses the ROUND<\/code> function to round the average salary of  employees in each department to the nearest integers.<\/p>\n\n\n
    SELECT<\/span>\n  department_name,\n  ROUND<\/span>(AVG<\/span>(salary), 0<\/span>) average_salary\nFROM<\/span>\n  employees e\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\nGROUP<\/span> BY<\/span>\n  department_name\nORDER<\/span> BY<\/span>\n  department_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
     department_name  | average_salary\n------------------+----------------\n Accounting       |          10150<\/span>\n Administration   |           4400<\/span>\n Executive        |          19333<\/span>\n Finance          |           8600<\/span>\n Human Resources  |           6500<\/span>\n IT               |           5760<\/span>\n Marketing        |           9500<\/span>\n Public<\/span> Relations |          10000<\/span>\n Purchasing       |           4150<\/span>\n Sales            |           9617<\/span>\n Shipping         |           5886<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary #<\/a><\/h2>\n\n\n\n
      \n
    • Use the ROUND<\/code> function to round a number to a specific precision.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
    Try it<\/a><\/p>\n\n\n\n
    Try it<\/a><\/p>\n\n\n
    Try it<\/a><\/p>\n\n\n
    Try it<\/a><\/p>\n\n\n
    Try it<\/a><\/p>\n\n\n