SQL CEIL() function

CEIL() function

SQL CEIL() function is used to get the smallest integer which is greater than, or equal to, the specified numeric expression.

Syntax:

CEIL(expression)

Parameters:

MySQL, PostgreSQL, and Oracle

All of above platforms support the SQL syntax of CEIL().

SQL CEIL() function: Visual presentation

SQL CEIL() function on positive value

To get the ceiling or nearest rounded up value of 17.36 from the DUAL table , the following SQL statement can be used :

-- This SQL statement selects the ceiling value of a negative number from the 'dual' table.

SELECT CEIL(-17.36) 
-- SELECT statement retrieves data from the database
-- CEIL() function returns the smallest integer greater than or equal to the specified number
-- In this case, CEIL() is applied to -17.36, resulting in -17 being the smallest integer greater than or equal to -17.36

FROM dual;
-- Specifies the 'dual' table, which is a special one-row, one-column table present in Oracle database
-- The 'dual' table is typically used for performing calculations or returning a single result

Explanation:

• This SQL query is intended to retrieve the ceiling value of a negative number.
• The CEIL() function is used to find the smallest integer greater than or equal to the specified number.
• In this case, CEIL() is applied to -17.36, resulting in -17 being the smallest integer greater than or equal to -17.36.
• The 'dual' table is used here to execute the query as it provides a convenient way to perform calculations or return single results without needing an actual table with data.
• The result of this query will be -17, as CEIL(-17.36) rounds up to the nearest integer, which is -17 in this case.

Output:

(CEIL(17.36))
-------------
           18

Example:

To get the ceiling or nearest rounded up value of -17.36 from the DUAL table, the following SQL statement can be used :

-- This SQL statement calculates the ceiling value of a negative number using the CEIL function and selects the result from the 'dual' table.

SELECT CEIL(-17.36) 
-- SELECT statement retrieves data from the database
-- CEIL() function is used to find the smallest integer greater than or equal to the specified number
-- In this case, CEIL() is applied to -17.36

FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries

Explanation:

• This SQL query is straightforward, as it's only a single statement.
• The purpose of this query is to find the ceiling value of a negative number, specifically -17.36.
• CEIL() is a mathematical function that rounds a number up to the nearest integer, returning the smallest integer that is greater than or equal to the given number.
• In this case, CEIL(-17.36) will return -17 because -17 is the smallest integer greater than or equal to -17.36.
• The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.

Output:

CEIL(-17.36)
------------
         -17

SQL CEIL() function with distinct

 
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

To get the unique ceiling or nearest rounded up value of the column 'commission' after multiplying by 75 from the 'agents' table, the following SQL statement can be used :

-- This SQL statement calculates the ceiling value of a specific expression involving commission amounts multiplied by 75, selecting distinct results from the 'agents' table.

SELECT DISTINCT(CEIL(commission*75)) "DISTINCT(CEIL())" 
-- SELECT statement retrieves data from the database
-- DISTINCT keyword ensures that only unique values are returned
-- CEIL() function is used to find the smallest integer greater than or equal to the specified number
-- The expression commission*75 is evaluated for each row, multiplying the commission by 75 before applying the CEIL() function

FROM agents;
-- Specifies the 'agents' table from which the data is being retrieved

Explanation:

• This SQL query is designed to calculate the ceiling value of a specific expression involving commission amounts multiplied by 75 for each row in the 'agents' table.
• The DISTINCT keyword ensures that only unique ceiling values are returned, preventing duplicates.
• The CEIL() function is used to find the smallest integer greater than or equal to the specified number.
• In this case, commission*75 calculates the commission amount multiplied by 75 before applying the CEIL() function.
• The query is executed against the 'agents' table to retrieve the required data.

Output:

DISTINCT(CEIL())
----------------
              11
              10
               9
              12

SQL: Comparing between CEIL() and FLOOR() function

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Here is a slide presentation which covers the SQL arithmetic functions.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: ABS
Next: FLOOR