ROUND() Function in SQL
The ROUND() function in SQL is used to round a numeric field to the nearest value with a specified number of decimal places. It takes two arguments: the number to be rounded and the number of decimal places to round to. If the second argument is omitted, the function rounds to the nearest whole number. Let's read more about Round() Function in SQL.
Table of contents
- Example 1: Round a number to 3 decimal places
- Example 2: Round positive numbers, negative numbers, and zero up to 2 decimal places
- Example 3: Using the ROUND() function with Variables
- Example 4: Round numeric data from a column
- Conclusion
As the name suggests, the ROUND() function in SQL is used to round off a decimal value to a specific decimal place. The ROUND() function in SQL has the following features:
• It accepts all positive and negative numbers as a parameter.
• It also accepts zero as a parameter.
• Fractions can also be fed to the ROUND() function as a parameter.
Syntax:
ROUND(number, decimals)
Parameters:
• number: This parameter corresponds to the number to be rounded.
• decimals: This argument corresponds to the number of decimal places to which the number is to be rounded.
Returns:
The ROUND() function in SQL returns the rounded number up to a specific decimal place.
Note: We will work on MySQL to demonstrate the ROUND() function. The parameters may vary depending upon your relation database provider.
Now, we have a basic understanding of what the ROUND() function in SQL does. Let’s take a look at a few example use cases.
Read Later
Best-suited PL/SQL courses for you
Learn PL/SQL with these high-rated online courses
Example 1: Round a number to 3 decimal places
SELECT ROUND(99.852413, 3);
Example 2: Round positive numbers, negative numbers, and zero up to 2 decimal places
— round positive number
SELECT ROUND(99.852413, 2);
— round negative number
SELECT ROUND(-99.852413, 2);
— round zero
SELECT ROUND(0, 2);
Output:
Example 3: Using the ROUND() function with Variables
Here we will declare two variables, namely my_number and my_decimal, and set values for them, respectively. Finally, we will use the ROUND() function with my_number and my_decimal as a parameter for rounding the number.
SET @my_number :=123.321;</p>SET @my_decimal := 2;</p>SELECT ROUND(@my_number, @my_decimal);</p>
Output:
Example 4: Round numeric data from a column
The ROUND() function can also be used to get the rounded values from a column. In the below example, we first create a table of grocery items and their respective prices using the below SQL query:
CREATE TABLE Grocery (</p>item_name TEXT NOT NULL,</p>selling_price DECIMAL(13,6) NOT NULL</p>
Now that we have created the table, let’s insert some data into it using the below commands:
INSERT INTO Grocery VALUES ('chips',15.333);</p>INSERT INTO Grocery VALUES ('Cheese',39.3347);</p>INSERT INTO Grocery VALUES ('Popcorn',77.3654);</p>INSERT INTO Grocery VALUES ('Mango',150.1548);</p>INSERT INTO Grocery VALUES ('Apples',333.378845);</p>INSERT INTO Grocery VALUES ('Brush',25.8477);</p>
Now let’s round the prices of each item to 2 decimal places using the below query:
SELECT item_name, selling_price, ROUND(selling_price, 2) AS Rounded_price FROM Grocery;
Conclusion
In this article, we have explored the ROUND() function in SQL. We have managed to look into parameters that go into the function.
This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio
Top Picks & New Arrivals
