MySQL CEIL() Function Explained By Practical Examples

Summary: in this tutorial, you will learn about the MySQL CEIL() function to round a numeric value up to the nearest integer

Introduction to the MySQL CEIL function

The CEIL() function takes an input number and returns the smallest integer greater than or equal to that number.

Here’s the basic syntax of the CEIL() function:

CEIL (numeric_expression)Code language: SQL (Structured Query Language) (sql)

In this syntax, the numeric_expression can be a literal number or an expression that evaluates to a number.

The type of the return number depends on the type of the input number. If the type of the input number is exact numeric or floating-point type, the type of the return value is exact numeric or floating-point type respectively.

Note that CEILING() is a synonym for CEIL() so you can use them interchangeably.

The following picture illustrates the CEIL() function:

MySQL CEIL() function examples

Let’s take some examples of using the CEIL() function.

1) Using CEIL() function with a positive number

The following example uses the CEIL() function that accepts a positive number:

SELECT CEIL(1.59);Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example, because the smallest integer greater than or equal to the input number is 2, the CEIL() function returns 2.

2) Using CEIL() function to a negative number

The following statement uses the CEIL() function that accepts a negative number:

SELECT CEIL(-1.59);Code language: SQL (Structured Query Language) (sql)

The query output is as follows:

The smallest integer greater than or equal to -1.59 is -1, therefore, the CEIL() function return -1.

3) Using MySQL CEIL() function in a query

We will use the products table from the sample database for the demonstration:

The following statement finds the average manufacturer’s suggested retail price (MSRP) of all products by product lines:

SELECT 
    productLine, 
    CEIL(AVG(msrp)) averageMsrp
FROM
    products
GROUP BY 
    productLine
ORDER BY
    averageMsrp;
Code language: SQL (Structured Query Language) (sql)

Output:

In this example, first, the AVG() function returned the average MSRP of all products as decimal numbers. Then, the CEIL() function returns the average MSRP as integer numbers.

Summary

Use the MySQL CEIL() function to return the smallest integer greater than or equal to the input number.

Was this tutorial helpful?