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)
