{"id":1041,"date":"2016-07-21T02:06:32","date_gmt":"2016-07-21T02:06:32","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1041"},"modified":"2025-03-16T23:14:20","modified_gmt":"2025-03-17T06:14:20","slug":"sql-ceil","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-math-functions\/sql-ceil\/","title":{"rendered":"SQL CEIL Function"},"content":{"rendered":"\n

The SQL CEIL<\/code> function accepts a number (or a numeric expression) and rounds it up to the nearest integer.<\/p>\n\n\n\n

Note that besides the CEIL<\/code> function, SQL also provides some function for rounding such as ROUND<\/code><\/a> and TRUNC<\/code> functions, which behave in a way similar to the CEIL<\/code> function.<\/p>\n\n\n\n

Some database systems such as SQL Server provides the CEILING<\/code> function that is equivalent to the CEIL<\/code> function.<\/p>\n\n\n\n

Syntax #<\/a><\/h2>\n\n\n\n

Here’s the 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
Arguments #<\/a><\/h2>\n\n\n\n
numeric_expression<\/p>\n\n\n\n
A floating-point value or a numeric expression that evaluates to a number.<\/p>\n\n\n\n
Return Value #<\/a><\/h2>\n\n\n\n
The CEIL<\/code> functions return an integer value.<\/p>\n\n\n\n
Examples #<\/a><\/h2>\n\n\n\n
The following example returns 101<\/code> because the nearest integer of 100.49<\/code> is 101<\/code>.<\/p>\n\n\n
SELECT<\/span> CEIL<\/span>(100.49<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n    101<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement returns -100<\/code> because it is the nearest integer value of -100.49<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  CEIL<\/span>(-100.49<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 ceil\n------<\/span>\n -100<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement returns 101<\/code> because it is the nearest integer of 100.51<\/code>:<\/p>\n\n\n
SELECT<\/span> CEIL<\/span>(100.51<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n    101<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"emp_dept_tables\"<\/figure>\n\n\n\n
The following example uses the CEIL<\/code> function to round the average salary of employees in each department.<\/p>\n\n\n
SELECT<\/span>\n  department_name,\n  CEIL<\/span>(AVG<\/span>(salary)) AS<\/span> average_salary\nFROM<\/span>\n  employees e\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\nGROUP<\/span> BY<\/span>\n  department_name\nORDER<\/span> BY<\/span>\n  department_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 department_name  | average_salary\n------------------+----------------<\/span>\n Accounting       |          10150\n Administration   |           4400\n Executive        |          19334\n Finance          |           8600\n Human Resources  |           6500\n IT               |           5760\n Marketing        |           9500\n Public Relations |          10000\n Purchasing       |           4150\n Sales            |           9617\n Shipping         |           5886<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the CEIL<\/code> function to round a round up a number to the nearest integer value.<\/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
See the employees<\/code> and departments<\/code> tables in the sample database<\/a>.<\/p>\n\n\n\n