MySQL ROUND() function
In this context, we will learn how we can use the MySQL ROUND() function with proper syntax and good examples.
Introduction of MySQL ROUND() function
The ROUND() function in MySQL is used to round a number to a specified number of decimal places. It will round off the number to the nearest integer when there no specified number of decimal places is provided for round-off.
Syntax of the MySQL ROUND() function
The syntax of the MySQL ROUND() function is given as follows:
ROUND(Y, Z);
Parameters or arguments used in MySQL ROUND() function:
There are two parameters accepted by the ROUND() function in MySQL, which are given as follows:
Y: The number which is to be rounded.
Z: Number of decimal places up to which the given number is to be rounded. It is optional. If not given, it rounds off the number to the closest integer. If it is negative, then the number is rounded to the left side of the decimal point.
Returns:
It will return the number after rounding to the specified places.
Application used for ROUND() function:
The ROUND() function can be used in the given below MySQL versions.:
- MySQL 5.7
- MySQL 5.6
- MySQL 5.5
- MySQL 5.1
- MySQL 5.0
- MySQL 4.1
- MySQL 4.0
- MySQL 3.23
Examples of MySQL ROUND() function:
Now we will look into some MySQL ROUND() function examples and will explore how we can use the ROUND function in MySQL.
Example 1:
Now we will Round off a number when D is not specified with the help of the ROUND function in MySQL.
a) In this example, we will Round off a Negative number.
SELECT ROUND(-8.11) AS Rounded_Number;
Output:
Rounded_Number
-8
1 row in set (0.00 sec)
b) In this example, we will Round off a Positive number.
SELECT ROUND(54.61) AS Rounded_Number;
Output:
Rounded_Number
54
1 row in set (0.00 sec)
Example 2:
Here, we will Round off a number when D is negative(-ve) with the help of the ROUND function in MySQL.
a) Rounding a Negative number.
SELECT ROUND(-1567.1100, -3) AS Rounded_Number;
Output:
Rounded_Number
-2000
1 row in set (0.00 sec)
b) Rounding a Positive number.
SELECT ROUND(1016.6089, -1) AS Rounded_Number;
Output:
Rounded_Number
1020
1 row in set (0.00 sec)
Example 3:
Here, we will Round off a number when D is positive(+ve) with the help of the ROUND function in MySQL.
a) Rounding a Negative number up to 2 decimal places.
SELECT ROUND(-1567.1160, 2) AS Rounded_Number;
Output:
Rounded_Number
-1567.12
1 row in set (0.00 sec)
b) Rounding a Positive number up to three decimal places.
SELECT ROUND(1016.6019, 3) AS Rounded_Number;
Output:
Rounded_Number
1016.602
1 row in set (0.00 sec)
Example 4:
When we require to find the rounded values for the column data, then we can take the help of the ROUND function. In this example, we are going to find rounded values for the Price column. For demonstration, we have created a table named ComROUNDity.
CREATE TABLE ComROUNDity(
ComROUNDity_id INT AUTO_INCREMENT,
ComROUNDity_name VARCHAR(100) NOT NULL,
Purchasing_price DECIMAL(13, 6) NOT NULL,
Selling_price DECIMAL(13, 6) NOT NULL,
Selling_Date Date NOT NULL,
PRIMARY KEY(ComROUNDity_id)
);
Now insert some data to the ComROUNDity table.
INSERT INTO
ComROUNDity(ComROUNDity_name, Purchasing_price, Selling_price, Selling_Date)
VALUES
('P6', 1060.865460, 1700.675400, '2020-08-26'),
('P2', 2000.154300, 3050.986700, '2020-08-27'),
('P1', 4000.874300, 5070.786500, '2020-08-28'),
('P2', 2090.654300, 3050.896500, '2020-09-01'),
('P3', 5900.543280, 7010.654700, '2020-09-04'),
('P4', 4000.353200, 4500.125400, '2020-09-05'),
('P5', 5010.768900, 6000.873200, '2020-09-08');
So, the ComROUNDity Table is –
| ComROUNDity_id | ComROUNDity_name | Purchasing_price | Selling_price | Selling_Date |
| 1 | P6 | 1060.865460 | 1700.675400 | 2020-08-26 |
| 2 | P2 | 2000.154300 | 3050.986700 | 2020-08-27 |
| 3 | P1 | 4000.874300 | 5070.786500 | 2020-08-28 |
| 4 | P2 | 2090.654300 | 3050.896500 | 2020-09-01 |
| 5 | P3 | 4000.353200 | 7010.654700 | 2020-09-04 |
| 6 | P4 | 4000.353200 | 4500.125400 | 2020-09-05 |
| 7 | P5 | 5010.768900 | 6000.873200 | 2020-09-08 |
Now, we are going to round off both Purchasing_price and Selling_price columns up to 2 decimal places.
SELECT ComROUNDity_name, Purchasing_price, ROUND(Purchasing_price, 2) Rounded_Bprice,
Selling_price, ROUND(Selling_price, 2) Rounded_Sprice
FROM ComROUNDity;
Output:
| ComROUNDity_name | Purchasing_price | Rounded_Bprice | Selling_price | Rounded_Sprice |
| P6 | 1060.865460 | 1060.87 | 1700.675400 | 1700.68 |
| P2 | 2000.154300 | 2000.15 | 3050.986700 | 3050.99 |
| P1 | 4000.874300 | 4000.87 | 5070.786500 | 5070.79 |
| P2 | 2090.654300 | 2090.65 | 3050.896500 | 3050.90 |
| P3 | 5900.543280 | 5900.54 | 7010.654700 | 7010.65 |
| P4 | 4000.353200 | 4000.35 | 4500.125400 | 4500.13 |
| P5 | 5010.768900 | 5010.77 |
Summary:
In the above context, we have learned how we can use the ROUND() function in MySQL used to round a number to a specified number of decimal places.