SQL - FLOOR() Function

---

The SQL FLOOR() is a numeric function in SQL that returns the largest integer value which is the smaller than or equal to the given number.

Suppose we have float values of 2.4 and 2.9, then the floor value of both is only 2. Because the floor value is always less than or equal to the given float number and never greater than the given float number.

Syntax

Following is the syntax of the SQL FLOOR() function −

SELECT FLOOR(DECIMAL_VALUE) AS alias_name;

Following is the syntax of the FLOOR function, which is useful for the column of a table −

SELECT FLOOR(int_column_name) AS alias_name FROM emp_tbl;

In the above syntax, we have to pass the integer column number from the table on which we want to perform the FLOOR() function.

Example

In the following example, we are demonstrating the FLOOR() function and flooring the decimal value, i.e. 1.5 −

SELECT
   FLOOR(1.5) as FLOOR_VALUE;

Output

When we execute the above SQL query, we get the nearest value of 1.5, i.e. 1.

+-------------+
| FLOOR_VALUE |
+-------------+
|           1 |
+-------------+

Example

In the following example, we are specifying a number, i.e., 10.9, to the FLOOR() function.

SELECT FLOOR(10.9) as 'floor_of_10.9';

Output

Following is the output of the above SQL query. We are getting 10 because floor always returns the largest integer, which is smaller than or equal to the specified value.

+---------------+
| floor_of_10.9 |
+---------------+
|            10 |
+---------------+

Example

In the following example, we are using the sales table to fetch the productname and cusstomername and perform the FLOOR() function. Here, we are calculating the floor value of the price column of the sales table.

Let’s create a table named sales −

CREATE TABLE sales (
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   Price DECIMAL(18,3),
   Location VARCHAR(255)
);

Let’s insert n data into table −

insert into sales values('Key-Board', 'Raja', 900.56, 'Hyderabad');
insert into sales values('Earphones', 'Roja', 2000.33, 'Vishakhapatnam');
insert into sales values('Mouse', 'Puja', 1500.66, 'Vijayawada');
insert into sales values('Mobile', 'Vanaja' , 9000.95, 'Chennai');
insert into sales values('Headset', 'Jalaja' , 6000.99, 'Goa');

Let’s fetch the table details.

SELECT * FROM sales;

Following is the sales table −

+-------------+--------------+--------+----------------+
| ProductName | CustomerName |  Price | Location       |
+-------------+--------------+--------+----------------+
| Key-Board   | Raja         | 900.56 | Hyderabad      |
| Earphones   | Roja         |2000.33 | Vishakhapatnam |
| Mouse       | Puja         |1500.66 | Vijayawada     |
| Mobile      | Vanaja       |9000.95 | Chennai        |
| Headset     | Jalaja       |6000.99 | Goa            |
+-------------+--------------+--------+----------------+

The following is the SQL query to fetch the details and calculate the floor value on the Price column −

SELECT
   ProductName, CustomerName,
   FLOOR(Price) as floor_value
   FROM sales;

Output

Following is the output of the above query −

+-------------+--------------+-------------+
| ProductName | CustomerName | floor_value |
+-------------+--------------+-------------+
| Key-Board   | Raja         |         900 |
| Earphones   | Roja         |        2000 |
| Mouse       | Puja         |        1500 |
| Mobile      | Vanaja       |        9000 |
| Headset     | Jalaja       |        6000 |
+-------------+--------------+-------------+