SQL - CEILING() Function

---

The SQL CEILING() function accepts a numeric value as an argument and returns the smallest (closest to negative infinity) integer value that is greater than or equal to this value.

In other words, the ceiling function ([x]) returns the upper limit value of a number given as the argument to it. Mathematically, this method is different from the approximation phenomenon as the ceiling function completely neglects the digits that occur after the decimal.

For example, [2.4] = 3, as the immediate smallest integer greater than the number 2.4 is 3. Similarly, [-1.3] will be equal to -1.

Syntax

Following is the syntax of SQL CEILING() function −

CEILING(x)

where, x is the value for which for which we need to find the celling.

Example

In the following example, we are trying to pass positive value as an argument to the CEILING() function −

SELECT CEILING(22.3) 
AS ceil

When we run above program, it produces following result −

+-------+
| ceil  |
+-------+
| 23    |
+-------+

Example

In the example given below, we are trying to pass negative value as an argument to the CEILING() function −

SELECT CEILING(-105.0238) 
AS ceil

Following is an output of the above code −

+-------+
| ceil  |
+-------+
| -105  |
+-------+

Example

We can also pass the mathematical constant PI as an argument to this function, it returns it's equivalent ceiling value.

SELECT CEILING(PI()) 
AS ceil

We know that the value of pi is 3.14, hence it's ceiling value will be 4 as shown in the output below −

+-------+
| ceil  |
+-------+
| 4     |
+-------+

Example

We can also pass numeric values as strings to this function as shown in the example below −

SELECT CEILING('763.872') 
AS ceil

Output of the above code is as follows −

+-------+
| ceil  |
+-------+
| 764   |
+-------+

Example

Assume we have created a table with name CUSTOMERS as shown below −

create table CUSTOMERS(ID INT NOT NULL, 
NAME VARCHAR(20) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS CHAR(25), 
SALARY DECIMAL(18, 2), 
PRIMARY KEY(ID));
Commands completed successfully.

Let us insert r values into it −

insert INTO CUSTOMERS VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2076.432);
insert INTO CUSTOMERS VALUES(2, 'Khilan', 25, 'Delhi', 1543.987);
insert INTO CUSTOMERS VALUES(3, 'kaushik', 23, 'Kota', 20321.04);
insert INTO CUSTOMERS VALUES(4, 'Chaitali', 25, 'Mumbai', 6509.45);
insert INTO CUSTOMERS VALUES(5, 'Hardik', 27, 'Bhopal', 8505.55);
insert INTO CUSTOMERS VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

Following query calculates the ceiling value of the salary of all the customers −

SELECT NAME,AGE,SALARY, 
CEILING(SALARY) 
AS ceilSalary
FROM customers;

The result produced is as follows −

+----------+-----+----------+------------+
| NAME     | AGE | SALARY   | ceilSalary |
+----------+-----+----------+------------+
| Ramesh   |  32 |  2000.00 | 2077       |
| Khilan   |  25 |  1500.00 | 1544       |
| kaushik  |  23 |  2000.00 | 20322      |
| Chaitali |  25 |  6500.00 | 6510       |
| Hardik   |  27 |  8500.00 | 8506       |
| Komal    |  22 |  4500.00 | 4500       |
| Muffy    |  24 | 10000.00 | 10000      |
+----------+-----+----------+------------+