MySQL FLOOR Function

MySQL FLOOR Function

The FLOOR() function in MySQL is a mathematical function that returns the largest integer less than or equal to a given number. It rounds the number down to the nearest whole number.

1. MySQL FLOOR() Syntax

FLOOR(number)

• number – The numeric value for which you want to find the floor.

2. Basic Example of FLOOR()

SELECT FLOOR(10.75);  -- Output: 10
SELECT FLOOR(-5.8);   -- Output: -6
SELECT FLOOR(3.99);   -- Output: 3
SELECT FLOOR(7);      -- Output: 7

✅ Explanation:

• FLOOR(10.75) returns 10 because 10 is the largest integer less than 10.75.
• FLOOR(-5.8) returns -6 because -6 is the largest integer less than -5.8.
• FLOOR(3.99) returns 3.
• FLOOR(7) remains 7 since it is already an integer.

3. FLOOR() in a MySQL Query

Example 1: Applying FLOOR() to a Column

SELECT product_name, price, FLOOR(price) AS rounded_price
FROM products;

✅ Rounds down the price of each product.

Example 2: Using FLOOR() for Pagination

SELECT * FROM orders 
LIMIT 10 OFFSET FLOOR(RAND() * 100);

✅ Selects 10 random rows with an offset using FLOOR().

Example 3: FLOOR() with Division to Group Data

SELECT 
    FLOOR(salary / 1000) * 1000 AS salary_range, COUNT(*) AS num_employees
FROM employees
GROUP BY salary_range;

✅ Groups employees into salary ranges (e.g., 1000–1999, 2000–2999).

4. Difference Between FLOOR() and CEIL()

Function	Input	Output
FLOOR(4.9)	4.9	4
CEIL(4.9)	4.9	5
FLOOR(-3.4)	-3.4	-4
CEIL(-3.4)	-3.4	-3

✅ FLOOR() rounds down, while CEIL() rounds up.

5. Conclusion

• FLOOR() is used to round numbers down to the nearest integer.
• Useful for grouping, pagination, and numerical processing.
• FLOOR() vs. CEIL(): FLOOR rounds down, CEIL rounds up.

🚀 Now you know how to use MySQL FLOOR() effectively!