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.