SQL - ROUND() Function

---

The SQL ROUND() is a mathematical function that is used to represent the floating-point number with the specified number of digits after the decimal point.

Suppose we have a float number 123.346 and we want to display the number that has only two digits after the decimal point, like 123.350, then we can do the same with the ROUND() function.

Syntax

Following is the syntax of the ROUND() function in SQL −

SELECT ROUND(number, decimals_places, operation) AS Alias_Name;

The round function accepts three parameters, two of which are required and one of which is optional.

• Number − it accepts the number that need to be rounded.

• Decimals_places − required the number of decimal places to round the number.

• Operation − It is optional; if 0 is entered, the result is rounded to the number of decimal places; if a value other than 0 is entered, the number of decimal places is truncated; the default value is 0.

Following is the syntax of the SQL ROUND() function that is used in the SQL and fetches the data from the created table.

SELECT ROUND(column_name, decimals_places, operation) AS Alias_Name FROM table_name;

In the above syntax, we are using the table name that we have created, and we have to define the name of the column on which we want to perform the ROUND() function.

Example

In the following example, we are rounding a floating point number to two decimal places.

Following is the query −

SELECT ROUND(123.1256, 2) AS roud_value;

Output

The output of the above query is shown below, which displays the given number and, after the second decimal point, 0 values will replace the other number.

+------------+
| roud_value |
+------------+
|   123.1300 |
+------------+

Example

In the following example, we are rounding the floating-point number to two decimal places and also using the operation parameter.

Following is the query −

SELECT ROUND(123.125, 2, 1) AS roud_value;

+------------+
| roud_value |
+------------+
|   123.1300 |
+------------+

Output

Following is the output of the above SQL query, which displays 0 after the two decimal points. If we set the operation parameter to 1, and the next value after the two decimal points is greater than 5, then the preceding number is not increased by one. It displays the same number.

Example

In the following example, we are rounding the salary column to two decimal places and fetching the name from the emp_tbl.

Let’s create a table named emp_tbl using the CREATE statement −

CREATE TABLE emp_tbl(ID INT NOT NULL, 
NAME VARCHAR(20), 
ACCOUNT_BL DECIMAL(10, 0), 
SALARY DECIMAL(18, 5));

Let’s insert the datas into the emp_tbl using the INSERT statement −

INSERT INTO emp_tbl VALUES(1, 'Raja', 1200, 1234.23450);
INSERT INTO emp_tbl VALUES(2, 'Vivek', 1500, 225.45600);
INSERT INTO emp_tbl VALUES(3, 'Roja', -1500, 3025.57600);
INSERT INTO emp_tbl VALUES(4, 'Lukha', -1700, 5065.16800);
INSERT INTO emp_tbl VALUES(5, 'Sonal', 1800, 10065.25400);

Let’s display the emp_tbl details using the SELECT statement −

SELECT * FROM emp_tbl

Following is the table −

+----+-------+------------+-------------+
| ID | NAME  | ACCOUNT_BL | SALARY      |
+----+-------+------------+-------------+
|  1 | Raja  |       1200 |  1234.23450 |
|  2 | Vivek |       1500 |   225.45600 |
|  3 | Roja  |      -1500 |  3025.57600 |
|  4 | Lukha |      -1700 |  5065.16800 |
|  5 | Sonal |       1800 | 10065.25400 |
+----+-------+------------+-------------+

Following is the SQL query −

SELECT NAME, ROUND(SALARY, 2) FROM emp_tbl;

Output

Following is the output of the above SQL query. We are getting the name and round of the salary.

+-------+------------------+
| NAME  | ROUND(SALARY, 2) |
+-------+------------------+
| Raja  |          1234.23 |
| Vivek |           225.46 |
| Roja  |          3025.58 |
| Lukha |          5065.17 |
| Sonal |         10065.25 |
+-------+------------------+