SQL Server CEILING() Function

Summary: in this tutorial, you will learn how to use the SQL Server CEILING function to round up a number to the nearest integer greater than or equal to the input number.

SQL Server CEILING function syntax

The CEILING() function allows you to round up a number to the nearest integer that is greater than or equal to an input number.

Here’s the syntax of the CEILING() function:

CEILING (number)Code language: SQL (Structured Query Language) (sql)

In this syntax, the number is a literal number or a numeric expression that you want to round.

The function returns a number whose type depends on the type of the input number. The following table illustrates the return type of the function based on the input type:

If the number is NULL, the function returns NULL.

SQL Server CEILING() function examples

Let’s take some examples of using the CEILING() function.

1) Using SQL Server CEILING() function with positive numbers

The following example uses the CEILING() function to round up positive numbers:

SELECT
  CEILING(11.51) x,
  CEILING(11.25) y;Code language: SQL (Structured Query Language) (sql)

Output:

x  | y
---+----
12 | 12Code language: SQL (Structured Query Language) (sql)

2) Using the CEILING function with negative numbers

The following statement uses the CEILING() function to round up negative numbers:

SELECT
  CEILING(-11.51) x,
  CEILING(-11.25) y;Code language: SQL (Structured Query Language) (sql)

Output:

x   | y
----+-----
-11 | -11
(1 row)Code language: SQL (Structured Query Language) (sql)

3) Using the CEILING function with money values

The following example uses the CEILING() function to round up the values of the money type:

SELECT
  CEILING($99.5) amount,
  CEILING($9.91) tax;Code language: SQL (Structured Query Language) (sql)

Output:

amount   | tax
---------+----------
100.0000 | 10.0000
(1 row)Code language: SQL (Structured Query Language) (sql)

4) Using the CEILING function with table data

We’ll use the production.products table from the sample database for the demonstration.

The following statement uses the CEILING() function to round up the average price by category id:

SELECT
  category_id,
  CEILING(AVG(list_price)) average_price
FROM
  production.products
GROUP BY
  category_id;Code language: SQL (Structured Query Language) (sql)

Output:

category_id | average_price
------------+---------------
1           | 288
2           | 683
3           | 731
4           | 2543
5           | 3282
6           | 1650
7           | 3176
(7 rows)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the CEILING() function to round up a number to the nearest integer greater than or equal to the input number.