What is ROUND function in SQL?

The 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 ROUND function

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

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:

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, 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:

For the first query = 46.16

For the second query = 0.00

Using the function parameter

The ROUND function has 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:

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 of 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:

SQL ROUND table

Getting the SUM of rounded column values

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

Query:

SQL ROUND SUM

The GROUP BY Example with 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:

SQL ROUND group by