MySQL CEIL

Introduction to MySQL CEIL

The MySQL CEIL() function is identical to the MySQL CEILING() function where it finds out the minimum integer result value that is either larger than or equivalent to a number. Both MySQL CEILING() and CEIL() functions are synonyms to each other and can be used interchangeably to take the numbers as inputs to return the respective integer value. Normally, among the Math functions in MySQL, the MySQL CEIL() function is one the mathematical function implemented to produce the top or identical value of the input given as argument to the function. We can provide both positive and negative numbers while applying the CEIL() function.

Syntax:

Following is the elementary simple syntax code to use the CEIL() function in MySQL:

CEIL(Numeric_Value)

• In the above syntax, the numeric_value term defines the required input for CEIL function argument. This value can be an expression that weighs to a number or a precise number.
• According to the type of the argument number provided as input, the result number type is specified. The type of output depends on the type of input specified in the CEIL() query.
• Suppose, if we have inputted a number with type as accurate number value or floating-point, then the result value type will be exactly as that of accurate number or floating-point simultaneously.

How CEIL Function works in MySQL?

Let us see the working of MySQL CEIL() function in MySQL:

For illustration, we are writing a query using CEIL() function as follows:

Code:

SELECT CEIL(20.50);

Here, the MySQL SELECT keyword statement is to be used together with the math function to display the result value by calculation of the number provided in the argument function having type as floating point.

Output:

So, it is clear that the CEIL() function will generate the smallest value that is either greater than or similar to 20.50 input value specified in the above query as 21.

Examples of MySQL CEIL

We will demonstrate an examples to show the uses of this math function in MySQL.

Example #1

We are now applying the CEIL() function for a positive number in the argument of the function. For this query, we will implement the following query statement.

Code:

SELECT CEIL(2.43);

Output:

In this above example, we come to know that the result produced has the smallest integer that is larger than or equal to the number given as input i.e. 2.43. Thus, the CEIL() function gives 3 as the result.

Code:

SELECT CEIL(24);

Output:

Code:

SELECT CEIL(1.25);

Output:

Code:

SELECT CEIL(0.56);

Output:

Code:

SELECT CEIL(500.08);

Output:

Code:

SELECT CEIL(0.0089);

Output:

Code:

SELECT CEIL(11.90);

Output:

Example #2

Let us apply the CEIL() function where we provide a negative number as input to produce the output as specified below.

Code:

SELECT CEIL(-2.43);

Output:

Code:

SELECT CEIL(-24);

Output:

Code:

SELECT CEIL(-1.25);

Output:

Code:

SELECT CEIL(-0.56);

Output:

Code:

SELECT CEIL(-500.08);

Output:

Code:

SELECT CEIL(-0.0089);

Output:

Code:

SELECT CEIL(-11.90);

Output:

Here, we can see that the output gives the number as result which is either bigger or identical to the input respectively by the MySQL CEIL() function. The CEIL() function provides the results for negative numbers as per above observations.

Example #3

Example of MySQL CEIL() function with a query in a table.

Suppose, we need a table for querying the column values against the CEIL() function and show its working in the database calculation. For this, we will create the table in the database using the query statement below.

Code:

CREATE TABLE Suppliers(Supplier_ID INT PRIMARY KEY AUTO_INCREMENT, CATEGORY VARCHAR(255) NOT NULL, Unit INT NOT NULL, CostEach FLOAT NOT NULL);

Also, we will insert some of the demo records for further process of query.

Code:

INSERT INTO Suppliers(Supplier_ID, Category, Unit, CostEach) VALUES
('10','Snacks','10','10.87'),
('11','Drinks','25','15.025'),
('12','Kitchen Needs','20','25.9');

Again, by the following we will display the contents of the table Suppliers using the SQL statement below.

Code:

SELECT * FROM Suppliers;

Output:

For CEIL() function query we will illustrate the evaluation using the table column from the table created above as Suppliers. The CEIL() function query for finding the ceil values of the total cost calculated for the supplier items implementing the query code as follows.

Code:

SELECT Category, CEIL(Unit * CostEach)TotalCost_CeilValue FROM Suppliers30 GROUP BY Category ORDER BY TotalCost_CeilValue;

Output:

The above statement provides the ceil values of the cost calculated for each supplier item by multiplying the unit column values with the cost price of each item associated in the Suppliers table. Firstly, in this query the cost total for each supplier item is calculated and then the resultant values are then used as inputs for the CEIL() function and then displays the smallest integer values which are greater than or similar to the input ones.

We should note that here in the table column named as CostEach contains the float type values therefore, result and total cost calculated for each supplier item will be in decimal form i.e. float type. After using the CEIL() function these float values will be produced as result values in integer values as you can view in the output above.

But let us see if we do not apply the CEIL() function to the previous query then, what the output will be.

For this we will change the query as follows.

Code:

SELECT Category, Unit * CostEach TotalCost FROM Suppliers30 GROUP BY Category ORDER BY TotalCost;

Output:

The output of the query code without the CEIL() function is as follows:

Thus, like the MySQL CEILING() function, the MySQL CEIL() function also works. So, the CEIL() function returns the least integer value but not less than the input number specified in the query function.

Conclusion

The MySQL CEIL() function performs as the mathematical technique where it rounds up the input number provided away from the zero. Actually, it generates the result integer value always up than the given argument value in the CEIL() function. MySQL CEIL() function gives the least integer value which is not smaller than the input value. Thus, it is useful in database to find out the upper values of any table column values for specific condition.

This is a guide to MySQL CEIL. Here we discuss the introduction to MySQL CEIL, how to use CEIL function along with query examples. You may also have a look at the following articles to learn more –

1. MySQL Admin Tool
2. MySQL REPLACE
3. MySQL REINDEX
4. MySQL ON DELETE CASCADE