MySQL ROUND()

In SQL language ROUND() function allows to round a numerical result. This function allows either to round without using a decimal to return an integer (i.e. no digits after the decimal point) or to choose the number of digits after the decimal point.
 

Syntax:

This mathematical function can be used in the following way:

SELECT ROUND(column_name)
FROM `table`

 

 
In this example, the column “column_name” contains numerical data (example: FLOAT, INT, NUMERIC …) and will return only integers.

To get the result with 2 digits of decimal it is necessary to specify the number of decimal desired as 2nd argument.

SELECT ROUND(column_name, 2)
FROM `table`

 

Example :

Let’s suppose a system that manages invoices and records each purchase in a database. This system uses an “invoices” table containing one row for each product. The table looks like the example below:

+------+----------+------------+-------+---------+
|  id  |   name   | invoice_id | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | Monitor  | 1          |  45   | 12.3521 |
|  102 | CD-ROM   | 1          |  100  | 25.3311 |
|  103 | Modem    | 1          |  12   | 11.0336 |
|  104 | Speaker  | 2          |  11   | 3.36578 |
|  105 | Pencil   | 3          |  148  | 2.32509 |
|  106 | Gum      | 4          |  250  | 1.92825 |
+------+----------+------------+-------+---------+

 

 

Round to the nearest integer:

It is possible to use ROUND() function to round the “price” column to an integer.

SELECT id, name, price, ROUND(price)
FROM invoices

Output:

+------+----------+---------+--------------+
|  id  |   name   |  price  | ROUND(price) |
+------+----------+---------+--------------+
|  101 | Monitor  | 12.35   | 12           |
|  102 | CD-ROM   | 25.33   | 25           |
|  103 | Modem    | 11.03   | 11           |
|  104 | Speaker  | 3.665   | 4            |
|  105 | Pencil   | 2.525   | 3            |
|  106 | Gum      | 1.928   | 2            |
+------+----------+---------+--------------+

This result shows that the function has removed the digits after the decimal point. It is interesting to note that the result is rounded to the nearest integer (rounded up to the next integer if the decimal places are greater than 0.5).
 

 

Rounding to 2 digits after the comma:

It is possible to round to 2 digits after the decimal point by using the following SQL query:

SELECT id, name, price, ROUND(price, 2)
FROM invoices

Output:

+------+----------+---------+--------------+
|  id  |   name   |  price  | ROUND(price) |
+------+----------+---------+--------------+
|  101 | Monitor  | 12.3521 | 12.35        |
|  102 | CD-ROM   | 25.3311 | 25.33        |
|  103 | Modem    | 11.0336 | 11.03        |
|  104 | Speaker  | 3.36578 | 4.37         |
|  105 | Pencil   | 2.32509 | 3.33         |
|  106 | Gum      | 1.92825 | 1.93         |
+------+----------+---------+--------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More