MySQL CEIL() Function

The CEIL() function, also known as CEILING(), returns the smallest integer value that is greater than or equal to the given number.

Syntax

CEIL(number)

Definition and Usage

• The CEIL() function rounds a number up to the nearest integer.
• If the number is already an integer, it returns the same value.
• This function is identical to the CEILING() function.

Parameter Values

Technical Details

• Works in: From MySQL 4.0
• Result: Returns an integer.

Examples

Example 1: Round up a decimal number

SELECT CEIL(25.75) AS Result;

Result:

Result: 26

Example 2: Round up an integer

SELECT CEIL(25) AS Result;

Result:

Result: 25

Example 3: Round up a negative number

SELECT CEIL(-4.2) AS Result;

Result:

Result: -4

Example 4: Use CEIL() with a computed value

SELECT CEIL(SUM(Price) / COUNT(*)) AS RoundedAverage
FROM Products;

Explanation: This calculates the average price and rounds it up to the nearest integer.

Usage Notes

1. Precision:

   • The CEIL() function always rounds up, regardless of the fractional part.
   • For example, CEIL(2.0001) returns 3.

2. Negative Values:

   • For negative numbers, CEIL() moves closer to 0.
   • Example: CEIL(-1.8) returns -1.

3. Performance:

   • Used commonly in financial calculations or pagination to round up required quantities.

Practical Query

Paginate query results

Suppose you want to calculate the total number of pages required to display 10 rows per page, given the total number of rows:

SELECT CEIL(COUNT(*) / 10.0) AS TotalPages
FROM Products;

Result:

TotalPages: 3

The CEIL() function is useful for scenarios where rounding up is required, such as in pagination, quantity calculations, and ensuring whole number values.