MySQL - ROUND() Function

The MySQL ROUND() function accepts (floating-point) number as a parameter and returns the rounded value of it. We can also pass an integer specifying the number of decimal places we can have in the resultant value.

Mathematically, the term "round off" describes the process of converting a decimal value to the closest integer value. In scenarios where an arithmetic expression returns a result in decimal values, rounding off the result to an integer will make calculations easier to understand.

A value can be rounded off to either highest integer or a lowest integer. If the given decimal value is greater than 0.5, then it will be rounded off to its highest integer; otherwise it will be rounded off to its lowest integer.

Syntax

Following is the syntax of MySQL ROUND() function −

ROUND(X, D);

Parameters

This function accepts two parameters: the numeric expression to be rounded, and an optional second parameter specifying the number of decimal places.

Return Value

This function returns the rounded value as a numeric or decimal value.

Example

In the following query, we are using the ROUND() function to round the number 5.6523 to the nearest integer −

SELECT ROUND(5.6523) As Result;

Output

The output for the query above is produced as given below −

Result
6

Example

We can also pass the arguments of this function as a string value −

SELECT ROUND('25748.67') As Result;

Output

This will produce the following result −

Result
25749

Example

We can pass negative values as arguments to this method −

SELECT ROUND(-5578.5778) As Result;

Output

The output is produced as follows −

Result
-5579

Example

This function also accepts an integer value representing the number of digits you can keep after the decimal −

SELECT ROUND(545895.6877562, 3) As Result;

Output

The output is produced as follows −

Result
545895.688

Example

In the example below, we are creating a MySQL table named CUSTOMERS using the CREATE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.56 ),
(2, 'Khilan', 25, 'Delhi', 1500.33 ),
(3, 'Kaushik', 23, 'Kota', 2000.66 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.95 ),
(5, 'Hardik', 27, 'Bhopal', 8500.99 ),
(6, 'Komal', 22, 'Hyderabad', 4500.11 ),
(7, 'Muffy', 24, 'Indore', 10000.50 );

Execute the below query to fetch all the inserted records in the CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.56
2 Khilan 25 Delhi 1500.33
3 Kaushik 23 Kota 2000.66
4 Chaitali 25 Mumbai 6500.95
5 Hardik 27 Bhopal 8500.99
6 Komal 22 Hyderabad 4500.11
7 Muffy 24 Indore 10000.50

Now, we use the MySQL ROUND() function to 

SELECT *, ROUND(SALARY) FROM CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY ROUND(SALARY)
1 Ramesh 32 Ahmedabad 2000.56 2001
2 Khilan 25 Delhi 1500.33 1500
3 Kaushik 23 Kota 2000.66 2001
4 Chaitali 25 Mumbai 6500.95 6501
5 Hardik 27 Bhopal 8500.99 8501
6 Komal 22 Hyderabad 4500.11 4500
7 Muffy 24 Indore 10000.50 10001
Advertisements