What is ROUND Function in SQL?

MS SQL Server and MySQL database ROUND function is used to return the number rounded to the specified precision.

For example, we want to get the rounded number for 499.89, this is how you may use the ROUND function at its basic:

SELECT ROUND(123.89, 0);

The output: 124.00

The second expression specifies the returned length after rounding the number.

The following example shows using the ROUND function queries with variations in SQL Server and MySQL.

A basic example of the ROUND function

In this SQL query of the ROUND function, I have given a number and provided the length argument. See the query and result:

SELECT ROUND(25.356, 1);

SQL ROUND

(You may test this query on SQL Server and MySQL).

Using a negative number in length example

This query shows the rounding and rounding approximations. Two queries are used to make you understand that:

SELECT ROUND(46.1556, 1);

SELECT ROUND(46.1556, -1);

SQL ROUND approx

 

The second query resulted in 50 because the precision is calculated from the left side of the decimal point for a negative number.

If it is a positive number, as in the first query, it is calculated from the right of the decimal point. If the given number is:

SELECT ROUND(43.1556, -1);

The result will be: 40

The reason is that the number towards the left is less than 5 while in the first case, it was 6 (which is bigger than 5).

If we use -2 in the above query, the result is as follows:

SELECT ROUND(46.1556, 2);

SELECT ROUND(43.1556, -2);

For the first query = 46.16

For the second query = 0.00

Using the function parameter

The ROUND function has a third parameter as well for which the default value is 0.

The zero default value means round the numeric expression.

If you specify a value other than 0, the numeric expression is truncated.

The following example shows the difference between rounding and truncation by using the third parameter:

SELECT ROUND(671.75, 0) As "Rounding"; 

SELECT ROUND(671.75, 0, 1) As "Truncation";

SQL ROUND trunc

The example of using ROUND with table data

In this example, I have applied the ROUND function to a table’s column. The demo table stores data for the employee salaries paid every month. For the demo, the data on salaries is entered with decimal points.

The ROUND function is used in the SELECT statement and see the query and resultant graphic below.

The Query:

SELECT ROUND(emp_sal_paid, 1) As "Rounded Salary" from sto_emp_salary_paid;

SQL ROUND table

Getting the SUM of rounded column values

The following query shows using the SUM function with ROUND for getting the total values after rounding the column. The same salary column is used as in the above example:

Query:

SELECT SUM(ROUND(emp_sal_paid, 1)) As "Total After Rounding"

FROM sto_emp_salary_paid;

SQL ROUND SUM

The GROUP BY Example with the ROUND function

The last example shows using the GROUP BY clause with the ROUND and SUM functions. So, we will get the rounded sum of each employee rather than the accumulative result of the whole column.

Query:

SELECT emp_name, SUM(ROUND(emp_sal_paid, 1)) As "Total After Rounding"

FROM sto_emp_salary_paid

Group by emp_name;

SQL ROUND group by

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!