MySQL CEIL() function

In this context, we will learn how we can use the MySQL CEIL () function with proper syntax and good examples.

Introduction of MySQL CEIL() function

Input is taken by the CEIL() function in MySQL, and it will give back the smallest integer greater than or equal to that number.

Syntax of the MySQL CEIL() function

The syntax of the MySQL CEIL () function is given as follows:

CEIL( y );

Description of MySQL CEIL() syntax:

In the above-written syntax, the num_ expression can be an expression or a literal number that evaluates to a number.

The type of the returned number depends on the type of the input number. The type of the return value will be the exact numeric or floating-point type when the type of the input number is numeric or floating–point type, respectively.

The important point to be noted is that CEIL() function is a synonym for the ceiling() function. So for that reason, we can also use them respectively.

Returns: It will return the nearest integer, which is >= y. So, it will return y when y is an integer. If not, then it will be the next integer which is greater than x. 

Application used for CEIL() function:

The CEIL() function can be used in the given below MySQL versions:

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Examples of MySQL CEIL() function:

Now, we will look into some examples of MySQL CEIL function.

Example 1:

Here, we will apply MySQL CEIL() function to a positive floating point number:

The example given below applies the CEIL () function to a positive floating point number:

Select CEIL( 1.69 );

Now, the output is:

CEIL( 1.69 )

2

In the above-written example, the CEIL() function will return 2 because the smallest integer is greater than or equal to the number 2.

Example 2:

Here, we will apply MySQL CEIL() function to a negative floating point number:

Select CEIL( -1.69 );

Now the output is:

CEIL( -1.69 )

-1

In the above written example, the CEIL() function will return -1 because the smallest integer is greater than or equal to the number -1.

Example 3:

Here, we will apply MySQL CEIL() function to a query: From the sample database, we will use a commodity table for the demonstration:

Commodity

Commoditycode Commodityname Commodityline Commodityscale Commodityvendor Commoditydescription Quantityinstock Buyprice Msrp

The statement given below derives the average manufactures suggested retail price (msrp) of all commodities by commodity line:

Select 
Commodityline,
CEIL( avg(msrp )) average msrp
From 
Commodity
Group by 
Commodityline
Order by
Averagemsrp ;

Now the output is:

Commodityline	Averagemsrp
Trains	54
Ships	65
Vintage cars	73
Planes	82
Motorcycles	94
Trucks and buses	107
Classic cars	115

In the above written example, first of all, the average msrp of all commodities as the decimal number is returned by the avg () function. After that, the CEIL() function returns the average msrp as an integer number.

Example 4:

Here, we will apply MySQL CEIL() function to a positive number:

Select CEIL( 6 ) as CEIL_ value ;

Now the output is:

CEIL_ value

6

Example 5:

Here, we will apply MySQL CEIL() function to a negative integer:

The example given below applies the CEIL() function to a negative integer:

Select CEIL( -4 ) as CEIL_ value ;

Now the output is:

CEIL_ value

-4

Example 6:

Here, we will find the CEIL value of a numeric column in a table.

y

7.5

1

0

-1

-2.5

Table: Number

Select y, CEIL(y) as y_CEIL from Number;

Output:

y	y_CEIL
7.5	8
1	1
0	0
-1	-1
-2.5	-2

Summary:

In this context, we have learned how we can use the MySQL () function to return the smallest integer greater than or equal to the number given.