How CEILING() function works in MySQL8?

With MySQL, we can do complex calculations very easily with the help of inbuilt mathematical functions. CEIL(number) and CEILING(number) works in a similar way to get the smallest integer value not less than input number.

---

CEILING(number) is a mathematical function. It returns the smallest integer value that is bigger than or equal to a number.

It returns NULL, if the input argument value is NULL.

It returns Warning, if the input argument is a string.

Note: it is a synonym for CEILING() function.

---

MySQL CEILING() : Syntax

---

MySQL CEILING() : Parameter

Name, Required /Optional, Type, Description
number, Required, Double, It represents a valid number.

---

MySQL CEILING() : Output

Return, Description
NULL, if the argument is NULL.
Double, It returns the smallest integer value that is bigger than or equal to a number.

---

MySQL CEILING()  Available from : MySQL 4.0

---

CEILING() Example 1 : Very Basic example

Below is a very easy example. See the value when we pass the input as a positive decimal value i.e 5.9635. It has returned the value 6 as output.

mysql> SELECT CEILING(5.9635);
+--------------+
| CEILING(5.9635) |
+--------------+
| 6 |
+--------------+
1 row in set (0.02 sec)

See the value when we pass the input as a negative decimal value i.e  -5.9635. It has returned the value –5 as output.

mysql> SELECT CEILING(-5.9635);
+---------------+
| CEILING(-5.9635) |
+---------------+
| -5 |
+---------------+
1 row in set (0.01 sec)

---

CEILING() Example 2 :

Below are some more examples.

mysql> SELECT CEILING(78);
+----------+
| CEILING(78) |
+----------+
| 78 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT CEILING(-78);
+-----------+
| CEILING(-78) |
+-----------+
| -78 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT CEILING(8.5678);
+--------------+
| CEILING(8.5678) |
+--------------+
| 9 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CEILING(-5.8900);
+---------------+
| CEILING(-5.8900) |
+---------------+
| -5 |
+---------------+
1 row in set (0.00 sec)

---

CEILING() Example 3 : It will return Warning if any string argument is passed.

See the below example :

mysql> SELECT CEILING('test');
+--------------+
| CEILING('test') |
+--------------+
| 0 |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'test' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

You can see the warning message with the help of the ‘SHOW WARNINGS’ command. Please refer above code for the same.

---

CEILING() Example 4 : NULL arguments

If the argument is NULL, it will return NULL. See the below example :

mysql> SELECT CEILING(NULL);
+------------+
| CEILING(NULL) |
+------------+
| NULL |
+------------+
1 row in set (0.01 sec)

---

CEILING() Example 5 : Using expressions

Here are some more examples to use the expression with the CEILING() function :

mysql> SELECT (2+1.52);
+----------+
| (2+1.52) |
+----------+
| 3.52 |
+----------+
1 row in set (0.00 sec)

The Sum of (2+1.52) expression is 3.52. We can also use the expression with CEILING() function. As in the below example you can see, for input 3.52, It has returned 4.

mysql> SELECT CEILING(3.52);
+------------+
| CEILING(3.52) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)

CEILING() with expressions.
mysql> SELECT CEILING(2+1.52);
+--------------+
| CEILING(2+1.52) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)

The Sum of (-2-1.52) expression is -3.52. We can also use the expression with CEILING() function. As in the below example you can see, for input -3.52, It has returned -3.

mysql> SELECT (-2-1.52);
+-----------+
| (-2-1.52) |
+-----------+
| -3.52 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT CEILING(-3.52);
+-------------+
| CEILING(-3.52) |
+-------------+
| -3 |
+-------------+
1 row in set (0.00 sec)

CEILING() with expressions.
mysql> SELECT CEILING(-2-1.52);
+---------------+
| CEILING(-2-1.52) |
+---------------+
| -3 |
+---------------+
1 row in set (0.00 sec)

---

CEILING() Example 6 : Using in the database query

We are using tbl_toysorderdetails table.

You can download it by clicking here. In the below query you can see in the output the difference between the values in order_amount and CEILING(order_amount) columns value. All the values in it have been CEILIED to integer values.

mysql> select client_firstname, order_amount, ceiling(order_amount) from tbl_toysorderdetails;

+------------------+--------------+-----------------------+
| client_firstname | order_amount | ceiling(order_amount) |
+------------------+--------------+-----------------------+
| Arnav            |     280.4000 |                281 |
| Aarav            |     500.6000 |                501 |
| Shanu            |     400.6000 |                401 |
| Manvi            |     800.2500 |                801 |
| Shouraya         |     560.8000 |                561 |
| Jeevesh          |    2578.8000 |               2579 |
| Jyoti            |     160.0000 |                160 |
| Ankita           |    2400.5601 |               2401 |
| Ankita           |    4578.8901 |               4579 |
| Aru              |    4500.6001 |               4501 |
+------------------+--------------+--------------------+
10 rows in set (0.00 sec)

---

Related articles : ABS(), CEIL(), MySQL Maths.