MySQL offers a variety of built-in functions for data manipulation. Previously, we have been talking about COUNT, DATEDIFF, and CONCAT. The upcoming tutorial will focus on the ROUND function, which is used to round numeric values for precision and accuracy. Rounding is vital in databases to avoid discrepancies in calculations, particularly with financial and scientific data. The article will provide practical examples and insights into effectively using the ROUND function for reliable results in dbForge Studio for MySQL.
Contents
- Syntax of MySQL ROUND
- 7 Examples of using ROUND in MySQL
- Example #1: Rounding when the decimal is not specified
- Example #2: Rounding when the decimal is positive
- Example #3: Rounding when the decimal is negative
- Example #4: Rounding to 0 decimal places
- Example #5: Rounding with the AVG function
- Example #6: Rounding with the SUM function
- Example #7: Find the rounded values for the table column
- Similar to MySQL ROUND()
- Conclusion
Syntax of MySQL ROUND
Before diving deeper into the intricacies of the ROUND function usage in MySQL, let us take a moment to get familiar with the basics. This function is used to round a numeric value to a specified number of decimal places or to the nearest integer. It is particularly useful when you wish to control the precision of the numeric values in your database. The simplified syntax will look somewhat like this:
ROUND(number, decimals);As you can see, the function takes two parameters:
number(required) — the numeric value that you would like to round. It can be a column name, a constant, or an expression representing the numeric value you wish to round.decimals(optional) — specifies the number of decimal places to which you would like to round the number. If you omit this parameter, the function will round the number to the nearest integer. If the decimal is positive, the rounding will be performed to that number of decimal places to the right of the decimal point. On the other hand, if the decimal is negative, the rounding will be done to the number of places to the left of the decimal point.
Note: the ROUND function does not modify the original value. Instead, it returns a new rounded value.
7 Examples of using ROUND in MySQL
The theory will take you only so far. Embracing new horizons through hands-on exploration yields the most profound insights.
In this venture, we turn to the adept guidance of dbForge Studio for MySQL, our chosen test site for the day. The Studio offers a comprehensive suite of features that streamline database management, query development, as well as performance optimization. From advanced query building to visual design tools, this IDE enhances efficiency and precision in MySQL tasks. Explore its rich capabilities such as intelligent code completion, syntax check, and formatting to elevate your database management experience.
Example #1: Rounding when the decimal is not specified
The first example will be the simplest derivation from the default template. Here, we will only use the required parameter along with the ROUND function, which is the number parameter. Let us take a random number and see what this query does to it:
SELECT ROUND(15.678) AS rounded_number;On executing the query, we see that the ROUND function rounds the number 15.678 to the nearest whole number. Since no decimal places are specified in the function call, the default behavior is to round to the nearest integer. As a result, the value 15.678 is rounded to 16 and then given the alias rounded_number in the query’s output:
Example #2: Rounding when the decimal is positive
The second example allows us to wonder yet another step further from the starting point. Now, we are set to introduce a positive decimal parameter ‘2’ into the ROUND function. This argument empowers the function to round the selected number, adhering precisely to the decimal places denoted by the parameter value:
SELECT ROUND(10.345, 2) AS rounded_number;As a result, the query “cuts” 10.345 to only have two numbers after the decimal point.
Example #3: Rounding when the decimal is negative
The third example is basically the same as the previous one, but with a twist: the decimal parameter is now negative 1:
SELECT ROUND(25.23, -1) AS rounded_number;When a negative value is provided as the decimal places argument, the function rounds the number to the left of the decimal point. In other words, it rounds to the nearest multiple of 10, 100, 1000, and so on, based on the absolute value of the argument.
In our particular case, when -1 is provided as the decimal places argument, the function rounds 25.23 to the nearest multiple of 10, resulting in 30.
Example #4: Rounding to 0 decimal places
The fourth example demonstrates the query’s behavior if we put a zero in place of the decimal parameter:
SELECT ROUND(7.9, 0) AS rounded_number;In this instance, the function rounds any given number to the nearest whole number. As you can see in the screenshot below, the value 7.9 appears as 8 in the rounded_number column.
Example #5: Rounding with the AVG function
For the fifth example, let us mix things up a bit and see how to work with the ROUND function when it comes to not just any random numbers but real data within your database. Say we run a movie-rental business, and it is time to gather some information in order to check how things are going for us so far. To begin with, we are going to check the average cost of each rental made with us. We will be using the sakila database for demonstration purposes:
SELECT ROUND(AVG(paybyrental), 2) AS avg_cost
FROM (SELECT
SUM(amount) PAYBYRENTAL
FROM payment AS p
GROUP BY rental_id) p;The query retrieves the sum of payment amounts for each rental (PAYBYRENTAL) using a subquery and then calculates the average of these sums rounded to two decimal places in the outer query. The final output is the rounded average payment made per rental.
Example #6: Rounding with the SUM function
Since we have already found out how much our customers are spending on average on each movie rental with us, let us now calculate the total amount of our earnings. For this, we will add the SUM function to the query:
SELECT ROUND(SUM(amount), 2) AS rounded_total
FROM rental;The query above adds up all the values in the amount column (representing the amounts spent on movie rentals) and rounds the total sum to two decimal places. As a result, we get a single column named rounded_total, displaying the rounded sum of all the amounts spent.
Example #7: Find the rounded values for the table column
The seventh example will be the last but not the least one. Here, we will select the titles of the movies for rent along with their rounded rental prices. To make the demonstration more visual, let us select the said values from the table just as they are:
SELECT title, rental_rate
FROM film;
As demonstrated in the screenshot provided, the rental prices exhibit a consistent pattern: each price is composed of a whole number followed by a decimal value of 99.
Moving on to adding the ROUND function to the equation:
SELECT title, ROUND(rental_rate, 0) AS rounded_price
FROM film;
On executing the query, we see two columns in the output: the title of the movie and its rental price. The difference lies within the second column. The ROUND function was applied to the values, resulting in their rounding to a whole number with no decimal places.
Note: In this article, we used dbForge Data Generator for MySQL to populate the mentioned tables with meaningful and diverse test data. It is a powerful GUI tool aimed at creating massive volumes of meaningful, realistic test data. The instrument includes a large collection of predefined data generators with customizable configuration options that allow populating MySQL database tables with random data. dbForge Data Generator functionality takes good care of the data generation process with its terrific 200+ realistic data generators. Moreover, the Enterprise edition of dbForge Studio allows you to use the built-in Data Generator without having to download extra solutions for this purpose.
Similar to MySQL ROUND()
MySQL is a complex multi-purpose relational database management system that offers various tools for the most specific tasks. Consequently, the ROUND function is not the only way to go about rounding the values:
- FLOOR
- CEIL or CEILING
- TRUNCATE
FLOOR() function
The FLOOR function rounds down the given numeric value and returns the largest integer less than or equal to the initial number.
SELECT FLOOR(15.678);
CEIL() or CEILING() function
The CEIL or CEILING function rounds up a given value. As a result, we get the smallest integer greater than or equal to the number we specified in the beginning.
SELECT CEIL(15.678);
TRUNCATE() function
The TRUNCATE function truncates a number to a specified number of decimal places, effectively removing the digits after the specified decimal places without rounding.
SELECT TRUNCATE(10.345, 1);
The main differences between these functions lie in how they handle rounding and the direction in which they adjust the number. FLOOR and CEIL always truncate toward the nearest lower or higher integer, respectively, without regard to the decimal part. As to the TRUNCATE, it simply cuts off the decimal part, while ROUND handles rounding to the nearest whole number based on the decimal part’s value.
Conclusion
In conclusion, the MySQL ROUND function is an essential tool for precision and accuracy in database management. It efficiently rounds numeric values to desired decimal places or the nearest integer, ensuring consistent results in your day-to-day calculations. dbForge Studio for MySQL, with its free 30-day trial version, provides a user-friendly platform to work with MySQL databases, making database development and administration more accessible and efficient. Do not miss the chance to enhance your database management – try the free trial today and unlock the full potential of your MySQL projects.
Useful links:
