SQL CEIL: Rounds Up to the Nearest Integer

The SQL CEIL function accepts a numeric expression and rounds up the argument 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

The following illustrates 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

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);
Code language: SQL (Structured Query Language) (sql)

 ceil
------
  101
(1 row)Code 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);Code language: SQL (Structured Query Language) (sql)

 ceil
------
 -100
(1 row)

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

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

 ceil
------
  101
(1 row)Code 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)

 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         |  5886
(11 rows)Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the CEIL function to round a float up to the nearest integer value.

Was this tutorial helpful ?