{"id":7122,"date":"2018-08-31T05:57:10","date_gmt":"2018-08-31T12:57:10","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7122"},"modified":"2023-10-07T02:20:43","modified_gmt":"2023-10-07T09:20:43","slug":"mysql-ceil","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-math-functions\/mysql-ceil\/","title":{"rendered":"MySQL CEIL Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the MySQL CEIL()<\/code> function to round a numeric value up to the nearest integer <\/p>\n\n\n\n

Introduction to the MySQL CEIL function<\/h2>\n\n\n\n

The CEIL()<\/code> function takes an input number and returns the smallest integer greater than or equal to that number.<\/p>\n\n\n\n

Here’s the basic syntax of the CEIL()<\/code> function:<\/p>\n\n\n

CEIL (numeric_expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the numeric_expression<\/code> can be a literal number or an expression that evaluates to a number.<\/p>\n\n\n\n
The type<\/a> 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.<\/p>\n\n\n\n
Note that CEILING()<\/code> is a synonym for CEIL()<\/code> so you can use them interchangeably.<\/p>\n\n\n\n
The following picture illustrates the CEIL()<\/code> function:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL CEIL() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the CEIL()<\/code> function.<\/p>\n\n\n\n
1) Using CEIL() function with a positive number<\/h3>\n\n\n\n
The following example uses the CEIL()<\/code> function that accepts a positive number:<\/p>\n\n\n
SELECT<\/span> CEIL<\/span>(1.59<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example, because the smallest integer greater than or equal to the input number is 2,\u00a0 the CEIL()<\/code> function returns 2.<\/p>\n\n\n\n
2) Using CEIL() function to a negative number<\/h3>\n\n\n\n
The following statement uses the CEIL()<\/code> function that accepts a negative number:<\/p>\n\n\n
SELECT<\/span> CEIL<\/span>(-1.59<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query output is as follows:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
The smallest integer greater than or equal to -1.59 is -1, therefore, the CEIL()<\/code> function return -1.<\/p>\n\n\n\n
3) Using MySQL CEIL() function in a query<\/h3>\n\n\n\n
We will use the products<\/code> table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"products<\/figure>\n\n\n\n
The following statement finds the average manufacturer’s suggested retail price (MSRP) of all products by product lines:<\/p>\n\n\n
SELECT<\/span> \n    productLine, \n    CEIL<\/span>(AVG<\/span>(msrp)) averageMsrp\nFROM<\/span>\n    products\nGROUP<\/span> BY<\/span> \n    productLine\nORDER<\/span> BY<\/span>\n    averageMsrp;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example, first, the AVG()<\/code> function returned the average MSRP of all products as decimal numbers. Then, the CEIL()<\/code> function returns the average MSRP as integer numbers.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL CEIL()<\/code> function to return the smallest integer greater than or equal to the input number.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t