MySQL FLOOR Function

MySQL FLOOR function is one of the Numeric Functions which returns the closest integer value that is less than or equal to the specified expression. The syntax of the FLOOR function is as shown below:

SELECT FLOOR (Numeric_Expression)
FROM Source

To demonstrate this MySQL FLOOR Numeric function, we are going to use the below shown data

MySQL FLOOR Function Example

The FLOOR returns the closest integer value less than the specified number, and the following query shows multiple ways to use this.

-- on Negative Value
SELECT FLOOR(-120.91) AS `Floor Value`;

-- on Positive Value
SELECT FLOOR(205.97) AS `Floor Value`;

-- on the string value
SELECT FLOOR('801.95') AS `Floor Value`;

-- on String
SELECT FLOOR('MySQL') AS `Floor Value`;

-- on NULLs
SELECT FLOOR('NULL') AS `Floor Value`;

From the below screenshot, you can see that we used this method on different values.

How to Floor Numeric Columns in a Table?

The FLOOR Numeric Function also allows you to find the closest value for the column data. In this example, we are going to find the values for all the records present in the Service Grade column.

SELECT Product, Color,
		StandardCost, FLOOR(StandardCost) AS Cost, 
        Sales, FLOOR(Sales) AS Sales, 
        TaxAmt, FLOOR(TaxAmt) AS Tax,
        ServiceGrade, FLOOR(ServiceGrade) AS Grade
FROM `numeric functions`;

How to use the FLOOR function in WHERE Clause?

In MySQL, we can also use this Numeric method in the Where Clause as well. In this example, we use this method in the where clause to restrict the records selected by the SELECT Statement.

SELECT Product, Color,
		StandardCost, FLOOR(StandardCost) AS Cost, 
        Sales, FLOOR(Sales) AS Sales, 
        TaxAmt, FLOOR(TaxAmt) AS Tax,
        ServiceGrade, FLOOR(ServiceGrade) AS Grade
FROM `numeric functions`
WHERE FLOOR(TaxAmt) > 200;