AppDividend
Latest Code Tutorials

SQL CEILING() Function: The Complete Guide

The ceiling() function returns the smallest integer greater than, or equal to, the specified numeric expression.

SQL CEILING()

SQL CEILING() is a built-in function used to return the smallest integer value that is greater than or equal to a number given as input. The ceiling() function returns the smallest integer value greater than or equal to a number.

Syntax

SELECT CEILING (Number);

Parameters

Number: The number whose ceiling value is to be calculated.

Examples

Query 1

SELECT CEILING (2.5);

Output

3

Explanation

Here, the ceiling value is returned.

Query 2

SELECT CEILING (1.3);

Output

2

Explanation

Here, the ceiling value is returned.

Query 3

SELECT CEILING (-1.3);

Output

-1

Explanation

Here, the ceiling value is returned, which is negative.

 Do not confuse why the output is not -2. This is because, like the already mentioned function returns, the value is greater or equal to a number, so -1 is greater than -1.3 so, the output is -1.

Query 4

SELECT CEILING (25.75 + 5.0);

Output

31

Explanation

Here, the ceiling value of the expression is returned.

Above were the common examples to illustrate the meaning of the CEILING function.

Let’s apply this function on a table Employee.

Employee

Emp_id First_name City State Salary_per_month
101 Rohit Patna Bihar 25895.75
201 Shivam Jalandhar Punjab 30122.45
301 Karan Allahabad Uttar Pradesh 99872.15
401 Suraj Kolkata West Bengal 88789.88
501 Akash Vizag Andhra Pradesh 98985.77

 

Suppose we want to remove the decimal point from the salary_per_month, then the ceiling function can be applied to it.

Query

Select Emp_id, First_name, City, State, 
CEILING (Salary_per_month) AS Salary_per_month from Employee;

Output

Emp_id First_name City State Salary_per_month
101 Rohit Patna Bihar 25896
201 Shivam Jalandhar Punjab 30123
301 Karan Allahabad Uttar Pradesh 99873
401 Suraj Kolkata West Bengal 88790
501 Akash Vizag Andhra Pradesh 98986

 

Explanation

Here, you can see that salary_per_month values previously contained decimal values have been removed.

MySQL CEILING()

The CEILING() function in MySQL returns the smallest integer value bigger than or equal to a number.

PostgreSQL CEILING()

The PostgreSQL ceiling() returns the value after rounding up any positive or negative decimal value as greater than the argument.

Conclusion

In SQL Server (Transact-SQL), the CEILING function returns the smallest integer value greater than or equal to a number.

See also

SQL FLOOR()

SQL LOG10()

SQL PI

SQL POWER()

SQL DEGREES()

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.