Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

ROUND() Function in MySQL

  • Last Updated : 01 Oct, 2020

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 :

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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_idProduct_nameBuying_priceSelling_priceSelling_Date
1P61060.8654601700.6754002020-08-26
2P22000.1543003050.9867002020-08-27
3P14000.8743005070.7865002020-08-28
4P22090.6543003050.8965002020-09-01
5P34000.3532007010.6547002020-09-04
6P44000.3532004500.1254002020-09-05
7P55010.7689006000.8732002020-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_nameBuying_priceRounded_BpriceSelling_priceRounded_Sprice
P61060.8654601060.871700.6754001700.68
P22000.1543002000.153050.9867003050.99
P14000.8743004000.875070.7865005070.79
P22090.6543002090.653050.8965003050.90
P35900.5432805900.547010.6547007010.65
P44000.3532004000.354500.1254004500.13
P55010.7689005010.776000.8732006000.87
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!