MySQL - ROUND() Function

Quiz

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

Print Page