SQL CEIL Function

The SQL CEIL function accepts a number (or a numeric expression) and rounds it up to the nearest integer.

Note that besides the CEIL function, SQL also provides some function for rounding such as ROUND and TRUNC functions, which behave in a way similar to the CEIL function.

Some database systems such as SQL Server provides the CEILING function that is equivalent to the CEIL function.

Syntax #

Here’s the syntax of the CEIL function:

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

Arguments #

numeric_expression

A floating-point value or a numeric expression that evaluates to a number.

Return Value #

The CEIL functions return an integer value.

Examples #

The following example returns 101 because the nearest integer of 100.49 is 101.

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

Output:

 result
--------
    101Code language: SQL (Structured Query Language) (sql)

The following statement returns -100 because it is the nearest integer value of -100.49:

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

Output:

 ceil
------
 -100Code language: SQL (Structured Query Language) (sql)

The following statement returns 101 because it is the nearest integer of 100.51:

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

Output:

 result
--------
    101Code language: SQL (Structured Query Language) (sql)

See the employees and departments tables in the sample database.

The following example uses the CEIL function to round the average salary of employees in each department.

SELECT
  department_name,
  CEIL(AVG(salary)) AS average_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
ORDER BY
  department_name;Code language: SQL (Structured Query Language) (sql)

Output:

 department_name  | average_salary
------------------+----------------
 Accounting       |          10150
 Administration   |           4400
 Executive        |          19334
 Finance          |           8600
 Human Resources  |           6500
 IT               |           5760
 Marketing        |           9500
 Public Relations |          10000
 Purchasing       |           4150
 Sales            |           9617
 Shipping         |           5886Code language: SQL (Structured Query Language) (sql)

Summary #

• Use the CEIL function to round a round up a number to the nearest integer value.