FLOOR()

Function Description

The FLOOR() function returns a number that is not greater than the corresponding digit of a certain number.

Function Syntax

> FLOOR(number, decimals)
> FLOOR(number)

Parameter definition

Parameters	Description
number	Required parameters, any currently supported numerical data
decimals	Optional parameter, representing the number of digits after the decimal point. The default value is 0, which means rounding to an integer, and when it is a negative number, rounding to the digits before the decimal point.

Example

drop table if exists t1;
create table t1(a int ,b float);
insert into t1 values(1,0.5);
insert into t1 values(2,0.499);
insert into t1 values(3,0.501);
insert into t1 values(4,20.5);
insert into t1 values(5,20.499);
insert into t1 values(6,13.500);
insert into t1 values(7,-0.500);
insert into t1 values(8,-0.499);
insert into t1 values(9,-0.501);
insert into t1 values(10,-20.499);
insert into t1 values(11,-20.500);
insert into t1 values(12,-13.500);

mysql> select a,floor(b) from t1;
+------+--------------+
| a | floor(b) |
+------+--------------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 20 |
| 5 | 20 |
| 6 | 13 |
| 7 | -1 |
| 8 | -1 |
| 9 | -1 |
| 10 | -21 |
| 11 | -21 |
| 12 | -14 |
+------+--------------+
12 rows in set (0.01 sec)

mysql> select sum(floor(b)) from t1;
+--------------------+
| sum(floor(b)) |
+--------------------+
| -6 |
+--------------------+
1 row in set (0.00 sec)

mysql> select a,sum(floor(b)) from t1 group by a order by a;
+------------------------------+
| a | sum(floor(b)) |
+------------------------------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 20 |
| 5 | 20 |
| 6 | 13 |
| 7 | -1 |
| 8 | -1 |
| 9 | -1 |
| 10 | -21 |
| 11 | -21 |
| 12 | -14 |
+------------------------------+
12 rows in set (0.00 sec)