ROUND() Function in MySQL

The ROUND() function in MySQL is used to round a number to a specified number of decimal places. If no specified number of decimal places is provided for round off, it rounds off the number to the nearest integer.

Syntax :

ROUND(X, D)

Parameter : This method accepts two parameters in the syntax, as mentioned above and described below –

  • X : The number which to be rounded.
  • D : Number of decimal places up to which the given number is to be rounded. It is optional. If not given it round 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 returns the number after rounded to the specified places.

Example-1 :
Rounding off a number when D is not specified.



  • Rounding a Negative number.
    SELECT ROUND(-10.11) AS Rounded_Number;

    Output :

    Rounded_Number
    -10
  • Rounding a Positive number.
    SELECT ROUND(100.61) AS Rounded_Number;

    Output :

    Rounded_Number
    101


Example-2 :
Rounding off a number when D is negative(-ve).

  • Rounding a Negative number.
    SELECT ROUND(-1567.1100, -3) AS Rounded_Number;

    Output :

    Rounded_Number
    -2000
  • Rounding a Positive number.
    SELECT ROUND(1016.6089, -1) AS Rounded_Number;

    Output :

    Rounded_Number
    1020


Example-3 :
Rounding off a number when D is positive(+ve).



  • Rounding a Negative number up to 2 decimal places.
    SELECT ROUND(-1567.1160, 2) AS Rounded_Number;

    Output :

    Rounded_Number
    -1567.12
  • Rounding a Positive number up to three decimal places.
    SELECT ROUND(1016.6019, 3) AS Rounded_Number;

    Output :

    Rounded_Number
    1016.602


Example-4 :
ROUND Function can also be used to find the rounded values for the column data. In this example, we are going to find rounded values for the Price column. To demonstrate create a table named Product.

CREATE TABLE Product(
    Product_id INT AUTO_INCREMENT, 
    Product_name VARCHAR(100) NOT NULL,
    Buying_price DECIMAL(13, 6) NOT NULL,
    Selling_price DECIMAL(13, 6) NOT NULL,
    Selling_Date Date NOT NULL,
    PRIMARY KEY(Product_id)
);

Now insert some data to the Product table –

INSERT INTO 
    Product(Product_name, Buying_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 Product Table is –

Product_id Product_name Buying_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 Buying_price and Selling_price column up to 2 decimal places.

SELECT Product_name, Buying_price, ROUND(Buying_price, 2) Rounded_Bprice, 
Selling_price, ROUND(Selling_price, 2) Rounded_Sprice
FROM Product;

Output :

Product_name Buying_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 6000.873200 6000.87
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.