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.