What is ROUND Function in SQL?

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);

(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);

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:

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";

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;

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;

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;