SQL Ceiling

Introduction to SQL Ceiling

SQL Ceiling function is the mathematical function available in SQL that is used for the numeric type of values. These numeric values can be either integers or floating-point numbers. If the numeric value is formatted inside the string type of value as parameter then that value is also allowed for Ceiling function. In short, any value or expression that can be deduced to the numeric value can be used as a parameter to the Ceiling() mathematical function in SQL. This function helps us retrieve the minimum integer value that is greater or equivalent to the passed value.

In this article, we will learn about how Ceiling function can be used to retrieve the integral value not less than the passed numeric value or expression, its syntax, and some of the examples that can help to make the implementation of the Ceiling() function easy and understandable.

Syntax:

The below is the syntax for the mathematical Ceiling() function in SQL –

Ceiling(expressionOrNumber);

• expressionOrNumber: expressionOrNumber can be any integer or floating-point value or even a decimal value. In case if this numeric value is wrapped into strings then these string values are also acceptable. Other than the direct specification of numeric value any expression that will ultimately deduce to a numeric value is also allowed as a parameter to the Ceiling() function.
• Return value: The return value of the Ceiling() function is the smallest integer value that is greater than or equal to the value that is passed as the parameter to the function. The type of the return value is dependent on the data type of the value that is passed as the parameter to the Ceiling() function. In case if the parameter is or integer data type then the return value is also of integer type. While in other cases if the deduced value of the parameter of the direct specification of the value of the parameter is of floating-point type then the datatype of the return value is of the floating-point data type itself.

Examples of SQL Ceiling

Following are the examples are given below:

1. Using Ceiling() function with Positive Values

We will consider the positive numeric value say 3.59 and then use the Ceiling function to retrieve the smallest integer value that is greater than or equivalent to the 3.59 value. Let us execute the following SQL query statement and observe the output –

SELECT Ceiling(3.59);

Output:

Let us consider one more example of positive value. But in this example, we will use an expression that will evaluate to the value that is of numeric type. Simply consider the expression 5 * 1.65 whose actual value is 8.25 and use this expression in Ceiling() function to retrieve minimum integral value greater than passed value using following query statement –

SELECT Ceiling(5 * 1.65);

Output:

Now, we will consider a positive number wrapped as a string and use it as a parameter to Ceiling() function. For example, consider “56.569” value that is used in the following manner –

SELECT Ceiling(56.569);

Output:

2. Using Ceiling() Function with Negative Values

We will consider the negative numeric value say -65.55 and then use the Ceiling function to retrieve the smallest integer value that is greater than or equivalent to the -65.55 value. Let us execute the following SQL query statement and observe the output –

SELECT Ceiling(-65.55);

Output:

Let us consider one more example of a negative value. But in this example, we will use an expression that will evaluate to the value that is of numeric type. Simply consider the expression 8 * -91.65 whose actual value is −733.2 and use this expression in Ceiling() function to retrieve minimum integral value greater than passed value using following query statement –

SELECT Ceiling(8 * -91.65);

Output:

Now, we will consider a negative number wrapped as a string and use it as a parameter to Ceiling() function. For example, consider “-98.154″value that is used in the following manner –

SELECT Ceiling(-98.154);

Output:

3. Using Ceiling() Function with Values in The Table

Let us now see how we can use the Ceiling() function in the query statements on the values of the columns of the table. We will create one table named workers using following query statement –

CREATE TABLE `workers` (
`developer_id` int(11) NOT NULL AUTO_INCREMENT,
`team_id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`position` varchar(100) DEFAULT NULL,
`technology` varchar(100) DEFAULT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`developer_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

Output:

Now, we will insert some records in workers table using the following query statements –

INSERT INTO `workers` VALUES
(1,1,'Payal','Developer','Angular',30000),
(2,1,'Heena','Developer','Angular',10000),
(3,3,'Vishnu','Manager','Maven',25000),
(4,3,'Rahul','Support','Digital Marketing',15000),
(5,3,'Siddhesh','Tester','Maven',20000),
(6,7,'Siddharth','Manager','Java',25000),
(7,4,'Brahma','Developer','Digital Marketing',30000),
(8,1,'Arjun','Tester','Angular',19000),
(9,2,'Nitin','Developer','SQL',20000),
(10,2,'Ramesh','Administrator','SQL',30000),
(11,2,'Rohan','Admin',NULL,20000),
(12,2,'Raj','Designer',NULL,30000);

Output:

Now we will calculate the average salary using the following query statement –

SELECT avg(salary) from workers;

Output:

If we want to retrieve the average salary in integer format with the greatest value that is greater than or equivalent to the average value using Ceiling() function using the following query statement –

SELECT Ceiling (avg(salary)) from workers;

Output:

Conclusion

Mathematical function Ceiling() is used rounding numeric values in SQL. We can retrieve the minimum value in integer format that is greater or equivalent to the passed numeric number or expression whose results can be either floating value of an integer or decimal value. The working of Ceiling() function is exactly different than Floor() function. Though both of them are used for rounding. Ceiling() funtion rounds up while Floor() function rounds down the numeric value.

Recommended Articles

This is a guide to SQL Ceiling. Here we also discuss the introduction and syntax of sql ceiling along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. PostgreSQL Link
2. SQL DELETE
3. MySQL Create Function
4. SQL DELETE ROW