Updated May 30, 2023
Introduction to MySQL ROUND
The round function in MySQL is used to round the numerical values in the database to a certain number of decimal places. Whenever we perform any aggregate functions on specific numerical values such as average, division, or product of the numbers, the resultant value may contain the number specified in the format that contains its value up to many digits after the decimal point to maintain the precision. Often, there is a requirement to display numerical values up to a certain number of digits after the decimal point or round the value to a whole integer number. In such cases, we can use the round function of MySQL to retrieve the numerical values in the desired format up to the desired decimal points and round off this value appropriately.
In this article, we will learn about the syntax of the round function and some examples that will help us know how to use the round function.
Syntax of MySQL ROUND
Below is the syntax:
ROUND(number,[decimal]);In the above syntax of ROUND, the function number stands for the numerical value that can be any expression or column value of constant value you wish to round off. Decimal is the digit representing the number of places up to which decimal value you have to round the number value. The decimal is the integer value that can be either positive or negative. When specified as a negative value, the number of digits before the decimal point will be zero, and the number will be rounded in the left side flow. Let us understand the working of positive and negative decimal parameter values with the help of an example.
Let us build a query statement that will have the positive integer value specified in the decimal parameter –
SELECT ROUND(454.1234,2);Execution of the above query gives the following output –
Now, let us study the output of the negative integer value as a decimal parameter using the below query –
SELECT ROUND(454.1234,-2);Execution of the above query gives the following output –
The output clearly shows that the value rounds off on the left side of the decimal place. As the decimal places up to which we have to round are -2, two digits from the left are made zero, while the third digit depends on the value of the second digit. If the value of the second digit is greater than or equal to 5, then increase the value of the third digit by one. Otherwise, do nothing. It stays the same.
Let us consider one more example of the negative integer value in the decimal parameter of a round function by using the following query statement –
SELECT ROUND(444.1234,-2);Execution of the above query gives the following output –
From the output, we can observe that the rounding to 2 decimal places in the negative value preserves the value of the third digit from the left. This is because the second decimal place contains the digit 4, which is less than the value 5.
If a value for the decimal parameter is not specified, it is assumed to be zero. Let us take one example.
SELECT ROUND(646.136,0);Execution of the above query gives the following output –
SELECT ROUND(646.136);Execution of the above query gives the following output –
Exact-value Number Rounding
The exact value number rounding makes use of the round to nearest rule. That is when the fractional part consists of a digit five or greater after the digit up to which the value will be round. The value is rounded up to the next integer value if the number is positive, while in the case of negative numbers, it is rounded down. Similarly, if the fractional part of the digit after the round decimal value is less than five, then the positive number of rounded down. In contrast, the negative number is rounded up.
Consider one example to clarify the concept.
If the digit after the round value is greater than or equal to 5 –
We round up a positive number.
SELECT ROUND(26.5);Execution of the above query gives the following output –
We round up a negative number.
SELECT ROUND(-26.5);Execution of the above query gives the following output –
If the digit after the round value is less than 5 –
We round up a positive number.
SELECT ROUND(26.4);Execution of the above query gives the following output –
We round up a negative number.
SELECT ROUND(-26.4);Execution of the above query gives the following output –
Approximate-value Number Rounding
The rounding of approximate-valued numbers follows the round-to-nearest even rule and depends on the C library. In the round-to-nearest even rule, when a number is exactly halfway between two integers, it rounds to the nearest even integer value.
Example of round function on column values and expressions –
Let us create one table named educba_writers that will contain the following columns mentioned in the create table query –
CREATE TABLE 'educba_writers' (
'id' int(11) NOT NULL,
'firstName' varchar(10) COLLATE latin1_danish_ci NOT NULL,
'rate' decimal(5,4) DEFAULT NULL,
'joining_date' date DEFAULT NULL,
'article_count' INTEGER DEFAULT 0,
'gst_percent' DECIMAL(5,2) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;that gives the following output –
Let us insert some records in it –
INSERT INTO 'educba_writers' ('id', 'firstName', 'rate', 'joining_date', 'article_count', 'gst_percent') VALUES
(1, 'Payal', '5.598', '2020-05-01',51,1.35),
(2, 'Vyankatesh', '7.6858', '2020-01-01',65,1.75),
(3, 'Omprakash', '6.6898', '2020-02-01',72,1.55),
(4, 'sakshi', '2.6559', '2020-06-01',49,1.95),
(5, 'prerna', '5.6858', '2020-02-01',72,1.55),
(6, 'preeti', '5.6258', '2020-06-01',49,1.95),
(7, 'sanjana', '4.5498', '2020-02-01',72,1.55),
(8, 'omkar', '4.6528', '2020-06-01',49,1.95),
(9, 'sohail', '6.6518', '2020-02-01',72,1.55),
(10, 'soniya', '8.5298', '2020-06-01',49,1.95),
(11, 'supriya', '7.1659', '2020-02-01',72,1.55),
(12, 'saniya', '7.5198', '2020-06-01',49,1.95),
(13, 'omkar', '4.6358', '2020-02-01',72,1.55),
(14, 'akshay', '9.6458', '2020-06-01',49,1.95),
(15, 'akash', '7.6948', '2020-02-01',72,1.55),
(16, 'siddharth', '8.4659', '2020-06-01',49,1.95);that gives the following output after execution –
Now, we have to calculate the GST amount that we need to deduct from the payment for each writer. For this, we will have to calculate the product of rate and number of articles that will be payment amount of articles. To calculate the GST to be deducted, you can multiply the rate, number of articles, and GST percentage. Hence, our query will be as follows –
SELECT firstName,(rate * article_count * gst_percent) as gst_deducted FROM educba_writers;
Now, we have to round this GST amount up to two places. So the query will
SELECT firstName,ROUND((rate * article_count * gst_percent),2) as gst_deducted FROM educba_writers;give the following output –
Conclusion
In MySQL, you can use the ROUND() function to round off numerical values of expressions or column values.
Recommended Articles
We hope that this EDUCBA information on “MySQL ROUND” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
