How to Round a Numeric Column to a Specified Decimal Count in SQL Server 2008 R2

This tutorial will show how to round a column to a specified decimal count. This is possible with the ROUND function. The ROUND function is used to round a numeric field to the number of decimals specified. It returns a numeric value, rounded to the length or precision specified by the programmer.

Setting Up

In this tutorial we will create a table called Products with columns of Prod_Id, ProductName, Unit, and UnitPrice. Prod_Id uses a data type of int, ProductName and Unit will be of the nvarchar data type, and UnitPrice will be of the float data type. To create a table in SQL Server, we use the CREATE TABLE statement.

 

 

Now that we have a table created, we can add data into it. To do this, we use the INSERT INTO, SELECT, and UNION statements. The INSERT INTO statement specifies what table and columns to insert values into, the SELECT statement is the data being inserted into the table, and the UNION statement prevents duplicates from being inserted into the table.

 

 

ROUND Function

So now that we have a table with data in it, we can start creating a query that will use the ROUND function to round the decimal point of the UnitPrice column. The goal we are aiming for is to round the decimal point of the UnitPrice to zero, meaning it will remove the decimal point.

 

 

The ROUND function has two arguments in it, UnitPrice and 0(zero). UnitPrice is the column we want to round and the zero represents how many decimal points we want the values to have.

Output

Execute the query and notice the new values of the UnitPrice column has no decimal point, as opposed to before when each value had a decimal point.

Now change the zero to 1 in the ROUND function and the UnitPrice values will now have a decimal point.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Leave a Reply

Your email address will not be published. Required fields are marked *