When working with numerical data in SQL, one of the most useful functions for mathematical operations is FLOOR(). It’s a simple yet powerful function that helps in rounding down numbers to the nearest integer. If you’re wondering how FLOOR works in SQL? Best FLOOR examples—this article will walk you through everything you need to know.
What Is the FLOOR() Function in SQL?
The FLOOR() function in SQL returns the largest integer that is less than or equal to a given number. In simpler terms, it rounds a decimal number down to its nearest whole number.
FLOOR() Syntax
The syntax of the FLOOR() function is straightforward:
FLOOR(number)- number – This is the numeric value that needs to be rounded down.
Basic Example of FLOOR()
Let’s start with a simple example. Suppose we have a floating-point number, and we want to apply FLOOR() to it.
SELECT FLOOR(7.8) AS Result;The output will be:
| Result |
|---|
| 7 |
As expected, 7.8 is rounded down to 7.
Using FLOOR() on Negative Numbers
FLOOR() also works with negative numbers. An important thing to remember is that it still rounds down to the nearest integer.
SELECT FLOOR(-5.4) AS Result;The output will be:
| Result |
|---|
| -6 |
Notice that -5.4 is rounded down to -6, not -5. This is because “-6” is the largest integer that is still less than or equal to -5.4.
FLOOR() in SQL Queries
FLOOR() is often used in real-world SQL queries to round down values in calculations. Consider a scenario where an online store tracks product prices, and we need to round down the prices of all products.
SELECT ProductName, Price, FLOOR(Price) AS RoundedPrice
FROM Products;This would return a table like:
| ProductName | Price | RoundedPrice |
|---|---|---|
| Smartphone | 399.99 | 399 |
| Headphones | 99.75 | 99 |
Generating Random Integers with FLOOR()
A common use case for FLOOR() is in conjunction with the RAND() function to generate random integers.
SELECT FLOOR(RAND() * 100) AS RandomInteger;This generates a random number between 0 and 99.
FLOOR() vs CEILING() – Understanding the Difference
SQL provides another function, CEILING(), which works in the opposite direction of FLOOR(). Instead of rounding down, it rounds up.
| Number | FLOOR() | CEILING() |
|---|---|---|
| 3.7 | 3 | 4 |
| -2.1 | -3 | -2 |
When Should You Use FLOOR() in SQL?
FLOOR() is useful in several situations, including:
- Price Adjustments – Removing decimal values for financial calculations.
- Random Number Generation – Restricting
RAND()output to integers. - Grouping and Binning – Placing values in range-based categories.
Conclusion
The FLOOR() function in SQL is a handy tool for rounding down numbers to the nearest integer. Whether you’re dealing with price adjustments, truncating values, or generating random integers, FLOOR() is an invaluable function.
Other interesting article:
How CEIL works in SQL? Best CEIL examples