SQL Standard

SQL FLOOR() Function

Mathematical operations are a common occurrence when dealing with databases. SQL provides us with a wide variety of functions and tools to perform various mathematic operations.

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(7.8);

SELECT FLOOR(9.3);

SELECT FLOOR(-4.6);

The previous example demonstrate how to use the “floor” function in MySQL to floor various numerical values.

The first example should round 7.8 to 7 and the second example should round 9.3 to 9.

NOTE: When we floor a negative number, the “floor” function rounds 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(7.8);

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 follows:

SELECT CAST(7.8 AS DECIMAL(5, 0));

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 it 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(7.8) FROM DUAL;

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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list