MySQL CEILING() Function

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

Syntax

CEILING(number)

Definition and Usage

• The CEILING() function rounds a number up to the nearest integer.
• If the number is already an integer, it will return the number itself.
• This function is equivalent to the CEIL() function in MySQL.

Parameter Values

Technical Details

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

Examples

Example 1: Round up a decimal number

SELECT CEILING(25.75) AS Result;

Result:

Result: 26

Example 2: Round up an integer

SELECT CEILING(25) AS Result;

Result:

Result: 25

Example 3: Round up a negative number

SELECT CEILING(-4.2) AS Result;

Result:

Result: -4

Example 4: Use CEILING() with a computed value

SELECT CEILING(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 CEILING() function always rounds the number up, regardless of the fractional part.
   • For example, CEILING(2.0001) returns 3.

2. Negative Values:

   • For negative numbers, CEILING() rounds up towards zero.
   • Example: CEILING(-1.8) returns -1.

3. Performance:

   • Commonly used in financial calculations, pagination, or when you need a whole number greater than or equal to a given number.

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 CEILING(COUNT(*) / 10.0) AS TotalPages
FROM Products;

Result:

TotalPages: 3

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