SQL Server ROUND()

SQL Server ROUND() Function

The SQL Server ROUND() function returns a number rounded to a specified number of decimal places. This function is useful for rounding numbers to the nearest integer or to a specific decimal place.

Syntax

SELECT ROUND(number, decimal_places);

The ROUND() function takes two arguments:

  • number: The number to be rounded.
  • decimal_places: The number of decimal places to which the number is to be rounded. If this value is negative, the number is rounded to the left of the decimal point.

Example SQL Server ROUND() Function Queries

Let's look at some examples of SQL Server ROUND() function queries:

1. Basic ROUND() Example

SELECT ROUND(123.4567, 2) AS rounded_value;

This query rounds the number 123.4567 to 2 decimal places. The result will be:

rounded_value
--------------
123.46

2. ROUND() with Negative Decimal Places

SELECT ROUND(123.4567, -1) AS rounded_value;

This query rounds the number 123.4567 to the nearest 10. The result will be:

rounded_value
--------------
120

3. ROUND() with a Column

SELECT price, ROUND(price, 1) AS rounded_price
FROM products;

This query rounds the values in the price column to 1 decimal place for each product in the products table. The result will show the original price and the corresponding rounded_price.

4. ROUND() with a Variable

DECLARE @num FLOAT, @decimals INT;
SET @num = 123.4567;
SET @decimals = 3;
SELECT ROUND(@num, @decimals) AS rounded_value;

This query uses variables to store the number and the number of decimal places and then returns the rounded value. The result will be:

rounded_value
--------------
123.457

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the ROUND() function.

Step 1: Creating a Table

This step involves creating a new table named products to store some sample data.

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 4)
);

In this example, we create a table named products with columns for id, name, and price.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the products table.

INSERT INTO products (id, name, price) VALUES (1, 'Product A', 123.4567);
INSERT INTO products (id, name, price) VALUES (2, 'Product B', 234.5678);
INSERT INTO products (id, name, price) VALUES (3, 'Product C', 345.6789);

Here, we insert data into the products table.

Step 3: Using the ROUND() Function

This step involves using the ROUND() function to round the values in the price column to 2 decimal places.

SELECT id, name, price, ROUND(price, 2) AS rounded_price
FROM products;

This query retrieves the id, name, price, and the rounded value of the price column for each row in the products table. The result will be:

id  name       price     rounded_price
--- ---------- --------- --------------
1   Product A  123.4567  123.46
2   Product B  234.5678  234.57
3   Product C  345.6789  345.68

Conclusion

The SQL Server ROUND() function is a powerful tool for returning a number rounded to a specified number of decimal places. Understanding how to use the ROUND() function and its syntax is essential for effective numeric calculations and data processing in SQL Server.