# 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:**

Name | Description |
---|---|

expression | An expression which is a numeric value or numeric data type.The bit data type is not allowed. |

**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

**Sample table : agents**

+------------+----------------------+--------------------+------------+-----------------+---------+ | 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.

**It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.**

https://www.w3resource.com/sql/arithmetic-functions/ceil-with-positive-value.php

**Weekly Trends and Language Statistics**- Weekly Trends and Language Statistics