MySQL ROUND

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 certain numerical values such as average, division, or product of the numbers then the resultant value may contain the number that is specified in the format that contains its value up to many digits after the decimal point to maintain the precision. Many times, it is required to display such numerical values up to some specific digits after the decimal point or even round the value to the 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 of the examples that will help us to know how we can use the round function.

Syntax:

ROUND(number,[decimal]);

In the above syntax of ROUND, function number stands for the numerical value that can be any expression, column value of constant value that you wish to round off. Decimal is the digit that represented 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 then the number of digits before the decimal point will be made 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 –

We can see from the output that the value is rounded 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 git is greater than or equivalent to 5 then the value of the third digit is increased by one else it stays the same.

Let us consider one more example of the negative integer value in the decimal parameter of round function by using the following query statement –

SELECT ROUND(444.1234,-2);

Execution of the above query gives the following output –

We can observe from the output that the value of the third digit from left remains intact even after rounding to 2 places negatively because the second place contained 4 that is less than 5 value in it.

The value of the decimal parameter when not specified is considered 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 the use of round to nearest rule that is when the fractional part consists of digit 5 or greater after the digit up to which the value is to be round then the value is rounded up to the next integer value if the number is positive while in case of negative numbers it is rounded down. Similarly, if the fractional part of the digit after the round decimal value is less than 5 then the positive number of rounded down while the negative number is rounded up.

Consider one example to clarify the concept.

If digit after the round value is greater than or equal to 5 –

A positive number is rounded up –

SELECT ROUND(26.5);

Execution of the above query gives the following output –

The negative number is rounded down –

SELECT ROUND(-26.5);

Execution of the above query gives the following output –

If digit after the round value is less than to 5 –

A positive number is rounded down –

SELECT ROUND(26.4);

Execution of the above query gives the following output –

The negative number is rounded up –

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 it also depends on the C library. In the round to nearest even rule the number is exactly split in halfway and then depending on the nearest even integer value the number is rounded off.

Example of round function on column values and expressions –

Let us create one table named educba_writers that will contain the following columns mentioned 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 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 to be deduced in payment for each of the writers. For this, we will have to calculate the product of rate and number of articles that will be payment amount of articles, and further, the GST to be deducted can be calculated by the product of 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 be

SELECT firstName,ROUND((rate * article_count * gst_percent),2) as gst_deducted FROM educba_writers;

that gives following output –

Conclusion

The ROUND() function is the mathematical function that can be used in MySQL to round off the numerical values of expressions or column values.

Recommended Articles

This is a guide to MySQL ROUND. Here we discuss Introduction, syntax, and Examples with code implementation. You can also go through our other related articles to learn more –

1. MySQL having
2. MySQL Data Type
3. MySQL Export Database
4. MySQL today()