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:
The output: 124.00
The second expression specifies the returned length after rounding the number.
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.
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:
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.
SELECT emp_name, SUM(ROUND(emp_sal_paid, 1)) As "Total After Rounding" FROM sto_emp_salary_paid Group by emp_name;