FLOOR()

FLOOR() rounds a number down to the specified decimal place, or to an integer when decimals is omitted.

Description

FLOOR() returns the greatest value at the specified decimal place that is less than or equal to the number. When decimals is omitted, it returns the greatest integer less than or equal to the number.

Syntax

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

Arguments

Arguments

Description

number

Required. Any numeric data type supported now.

decimals

Optional constant integer specifying the number of decimal places. The default is zero, which rounds down to an integer. A negative value rounds down at a position to the left of the decimal point.

Examples

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)