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.