How the FLOOR() function works in Mariadb?

The FLOOR() function is a mathematical function that returns the largest integer value that is less than or equal to a given number.

Posted on

The FLOOR() function is a mathematical function that returns the largest integer value that is less than or equal to a given number. It can be used to round down a number to the nearest integer, or to truncate the decimal part of a number.

Syntax

The syntax of the FLOOR() function is as follows:

FLOOR(X)

The function takes one argument:

  • X is the number to be rounded down.

The function returns an integer value that is the result of rounding down X. If X is NULL, the function returns NULL.

Examples

Example 1: Using FLOOR() function with positive numbers

The following example shows how to use the FLOOR() function with positive numbers:

SELECT FLOOR(1.5);
SELECT FLOOR(3.14);
SELECT FLOOR(5.99);

The function returns the largest integer that is less than or equal to each number.

+------------+
| FLOOR(1.5) |
+------------+
|          1 |
+------------+

+-------------+
| FLOOR(3.14) |
+-------------+
|           3 |
+-------------+

+-------------+
| FLOOR(5.99) |
+-------------+
|           5 |
+-------------+

Example 2: Using FLOOR() function with negative numbers

The following example shows how to use the FLOOR() function with negative numbers:

SELECT FLOOR(-1.5);
SELECT FLOOR(-3.14);
SELECT FLOOR(-5.99);

The function returns the largest integer that is less than or equal to each number.

+-------------+
| FLOOR(-1.5) |
+-------------+
|          -2 |
+-------------+

+--------------+
| FLOOR(-3.14) |
+--------------+
|           -4 |
+--------------+

+--------------+
| FLOOR(-5.99) |
+--------------+
|           -6 |
+--------------+

Example 3: Using FLOOR() function with zero

The following example shows how to use the FLOOR() function with zero:

SELECT FLOOR(0);

The function returns zero, because zero is the largest integer that is less than or equal to zero.

+----------+
| FLOOR(0) |
+----------+
|        0 |
+----------+

Example 4: Using FLOOR() function with NULL

The following example shows how to use the FLOOR() function with NULL:

SELECT FLOOR(NULL);

The function returns NULL, because NULL is not a valid number.

+-------------+
| FLOOR(NULL) |
+-------------+
|        NULL |
+------------+

Example 5: Using FLOOR() function with expressions

The following example shows how to use the FLOOR() function with expressions. Suppose we have a table called sales that has the following data:

+----+---------+-------+--------+
| id | product | price | amount |
+----+---------+-------+--------+
|  1 | A       | 10.50 |     10 |
|  2 | B       | 20.00 |     15 |
|  3 | C       | 15.75 |     12 |
|  4 | D       | 25.00 |     20 |
|  5 | E       | 30.00 |     25 |
+----+---------+-------+--------+

We can use the FLOOR() function to calculate the total revenue of each product, by multiplying the price and the amount, and rounding down the result to the nearest integer:

SELECT product, price, amount, FLOOR(price * amount) AS revenue
FROM sales;

The function returns the revenue of each product as an integer value.

+---------+-------+--------+---------+
| product | price | amount | revenue |
+---------+-------+--------+---------+
| A       | 10.50 |     10 |     105 |
| B       | 20.00 |     15 |     300 |
| C       | 15.75 |     12 |     189 |
| D       | 25.00 |     20 |     500 |
| E       | 30.00 |     25 |     750 |
+---------+-------+--------+---------+

Some of the functions that are related to the FLOOR() function are:

  • CEIL() function: This function returns the smallest integer value that is greater than or equal to a given number. It can be used to round up a number to the nearest integer, or to add one to the integer part of a number. For example, CEIL(1.5) returns 2, and CEIL(-1.5) returns -1.
  • ROUND() function: This function returns a number rounded to a specified number of decimal places. It can be used to round a number to a desired precision, or to remove the decimal part of a number. For example, ROUND(1.567, 2) returns 1.57, and ROUND(1.567, 0) returns 2.
  • TRUNCATE() function: This function returns a number truncated to a specified number of decimal places. It can be used to remove the fractional part of a number without rounding. For example, TRUNCATE(1.567, 2) returns 1.56, and TRUNCATE(1.567, 0) returns 1.

Conclusion

The FLOOR() function is a useful mathematical function that can be used to round down a number to the nearest integer, or to truncate the decimal part of a number. It returns the largest integer value that is less than or equal to a given number. It can be used with positive or negative numbers, zero, or NULL. It can also be used with expressions to perform calculations and round down the results.