ROUND Function in SQL
The SQL ROUND function rounds the specified number till the particular decimal places.
Syntax of ROUND String Function
In this ROUND function, following are the three arguments:
- Number: It is that decimal number which is to be rounded. Decia
- Decimal_places: It can be positive or negative integer which shows the number of decimal places to be round.
- Operation: It is optional.
In Structured Query Language, we can also use the ROUND function with the integer columns of the table as shown in the following block:
In this syntax, we used the ROUND function with existing table of SQL. Here, we have to define the name and columns of that table on which we want to perform the ROUND function.
Examples of ROUND String function
Example 1: The following round function rounds the number to -1 decimal places:
Output:
Round_-1Value |
---|
950.00 |
Example 1: The following round function rounds the number to 2 decimal places with third argument which is non-zero:
Output:
Round_-1Value |
---|
145.41 |
Example 3: This example uses the mathematical ROUND function with the SQL table:
In this third example, we will create the new table through which we will perform the ROUND function with the integer columns of the table:
The following shows the syntax to create the new table in SQL:
The following CREATE statement creates the Product_Details table for storing the price and quantity of products:
The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:
The following SELECT statement displays the inserted records of the above Product_Details table:
Product_ID | Product_Name | Product_Quantity | Purchasing_Price | Selling_Price | Release_Date | Product_Rating |
---|---|---|---|---|---|---|
104 | P1 | 10.250 | 945.2548 | 1050.2547 | 2022-04-30 | 8 |
202 | P4 | 15.500 | 45.248 | 75.5725 | 2022-01-28 | 5 |
103 | P2 | 18.250 | 255.248 | 475.725 | 2022-02-18 | 4 |
111 | P7 | 25.250 | 5.958 | 15.955 | 2021-12-25 | 9 |
210 | P6 | 12.650 | 50.958 | 70.955 | 2021-10-15 | 11 |
212 | P8 | 19.750 | 110.850 | 250.955 | 2022-01-10 | 3 |
112 | P10 | 24.950 | 550.654 | 835.657 | 2022-04-11 | 8 |
Query 1: The following SELECT query uses the ROUND function with the Product_Quantity column of the above Product_Details table:
Output:
Product_ID | Product_Name | Product_Quantity | Round_-1quantity |
---|---|---|---|
104 | P1 | 10.250 | 10.000 |
202 | P4 | 15.500 | 20.000 |
103 | P2 | 18.250 | 20.000 |
111 | P7 | 25.250 | 30.000 |
210 | P6 | 12.650 | 10.000 |
212 | P8 | 19.750 | 20.000 |
112 | P10 | 24.950 | 20.000 |
Query 2: The following SELECT query uses the ROUND function with the Purchasing_Price and Selling_Price columns of those products whose Product_ID is greater than 103 in the above Product_Details table:
Output:
Product_ID | Purchasing_Price | Round_2purcahse | Selling_Price | Round_2Sellling |
---|---|---|---|---|
104 | 945.2548 | 945.2500 | 1050.2547 | 1050.2500 |
202 | 45.248 | 45.250 | 75.5725 | 75.5700 |
111 | 5.958 | 5.960 | 15.955 | 15.960 |
210 | 50.958 | 50.960 | 70.955 | 70.960 |
212 | 110.850 | 110.850 | 250.955 | 250.960 |
112 | 550.654 | 550.650 | 835.657 | 835.660 |
Query 3: The following SELECT query uses the ROUND function with the Product_Quantity column of the above Product_Details table:
This function uses the non-zero value, i.e., 1 to the third argument in round function.
Output:
Product_ID | Product_Quantity | Round_1_1quantity |
---|---|---|
104 | 10.250 | 10.200 |
202 | 15.500 | 15.500 |
103 | 18.250 | 18.200 |
111 | 25.250 | 25.200 |
210 | 12.650 | 12.600 |
212 | 19.750 | 19.700 |
112 | 24.950 | 24.900 |