One such task includes rounding numbers. Rounding down, also known as flooring, refers to a common mathematical operation that involves adjusting a numerical value to the next nearest lower whole number or a specific decimal place.
Rounding down typically involves truncating the decimal part of a given number or moving towards zero on the basic number line. A common use of flooring or rounding down is simplifying a number to a whole number or specific precision.
Let us explore how to round down in various SQL databases.
Round Down in MySQL/SQL Server
To round down in MySQL and SQL Server, we can use the floor() function to floor a number to the nearest integer of a given decimal value.
An example usage is as follows:
SELECT FLOOR(9.3);
SELECT FLOOR(-4.6);
The given examples demonstrate how to use the floor function in MySQL to floor various numerical values.
The first example should round 7.8 to 7, the second should round 9.3 to 9.
NOTE: When we floor a negative number, the floor function will round the number to a more negative integer.
Round Down in PostgreSQL
Like MySQL, PostgreSQL also provides the FLOOR() function to floor a given number to the nearest integer or decimal.
Using the FLOOR() function:
SELECT FLOOR(9.3);
SELECT FLOOR(-4.6);
This should return similar results as the MySQL example.
We can also use the CAST() function in PostgreSQL to floor a given value. Examples are as shown in the following:
SELECT CAST(9.3 AS DECIMAL(5, 0));
SELECT CAST(-4.6 AS DECIMAL(5, 0));
In this case, we use the cast() function so specify the number of decimal places for which we wish to round. In our case, we round to 0 decimal places.
Round Down in Oracle
Oracle provides us the FLOOR() function to round down to the nearest integer. An example usage is as follows:
SELECT FLOOR(9.3) FROM DUAL;
SELECT FLOOR(-4.6) FROM DUAL;
As with other databases, FLOOR() in Oracle rounds the numbers down to the nearest integer or a lower integer for negative numbers.
Conclusion
In this short post, we learned how to round down, also known as flooring, in SQL databases using the floor() or cast() functions whenever applicable.
