SQLScalar and Aggregate Functionsbeginner45 min read

Numeric Functions

Perform mathematical calculations using ROUND, TRUNC, CEIL, FLOOR, ABS, MOD, and POWER.

On This Page (Table of Contents)

In this lesson, you will learn

✓Understand how numeric functions transform numbers in SQL
✓Round decimal values using ROUND
✓Remove decimal digits using TRUNC without rounding
✓Calculate upper and lower integer bounds using CEIL and FLOOR
✓Calculate absolute values and remainders using ABS and MOD
✓Perform exponentiation using POWER
✓Handle NULL values and understand numeric precision
✓Apply numeric functions in pricing, reporting, and data analysis queries

1. Introduction to Numeric Functions

Numeric functions allow SQL to perform mathematical operations directly inside a query. Instead of retrieving raw values and performing every calculation in your application, you can calculate, round, or transform numbers in the database.

For example, an online store may need to calculate discounted prices, a reporting system may need to round averages, and an analytics application may need to calculate remainders or powers.

This lesson uses PostgreSQL syntax. Some numeric functions exist in other database systems too, but their supported argument types and exact behavior can differ.

The main functions in this lesson are:

- ROUND: rounds a number to a specified precision.
- TRUNC: removes digits beyond a specified precision without rounding.
- CEIL: returns the smallest integer greater than or equal to a number.
- FLOOR: returns the largest integer less than or equal to a number.
- ABS: returns the absolute value.
- MOD: returns the remainder of division.
- POWER: raises a number to an exponent.

2. ROUND: Rounding Numbers

ROUND rounds a numeric value to the nearest value at the requested precision.

Syntax:

ROUND(number)
ROUND(number, decimal_places)

The second argument is optional for numeric values. If omitted, the result is rounded to zero decimal places.

Examples:

Using ROUND

sql

1
2
3
4
SELECT
    ROUND(12.345::numeric, 2) AS two_decimals,
    ROUND(12.345::numeric, 1) AS one_decimal,
    ROUND(12.345::numeric) AS whole_number;

Output

two_decimals | one_decimal | whole_number --------------+-------------+------------- 12.35 | 12.3 | 12

ROUND returns 12.35 at two decimal places, 12.3 at one decimal place, and 12 when rounded to a whole number. The numeric cast makes the example's decimal behavior explicit.

3. Understanding ROUND with Positive and Negative Precision

The second argument of ROUND controls the position at which rounding occurs.

- ROUND(value, 2): rounds to two digits after the decimal point.
- ROUND(value, 1): rounds to one digit after the decimal point.
- ROUND(value, 0): rounds to a whole number.
- ROUND(value, -1): rounds to the nearest ten.
- ROUND(value, -2): rounds to the nearest hundred.

Example:

Rounding to Tens and Hundreds

sql

1
2
3
4
SELECT
    ROUND(146::numeric, -1) AS nearest_ten,
    ROUND(146::numeric, -2) AS nearest_hundred,
    ROUND(149.75::numeric, 0) AS whole_number;

Output

nearest_ten | nearest_hundred | whole_number ------------+-----------------+------------- 150 | 100 | 150

A negative precision rounds digits to the left of the decimal point. PostgreSQL's ROUND behavior for NUMERIC is used here.

4. TRUNC: Removing Decimal Digits

TRUNC removes digits beyond a specified decimal position without rounding the remaining digits.

Syntax:

TRUNC(number)
TRUNC(number, decimal_places)

Compare ROUND(8.789, 2) with TRUNC(8.789, 2):

- ROUND produces 8.79.
- TRUNC produces 8.78.

TRUNC is useful when the requirement is to discard extra decimal digits rather than round the value.

ROUND Versus TRUNC

sql

1
2
3
4
SELECT
    ROUND(8.789::numeric, 2) AS rounded_value,
    TRUNC(8.789::numeric, 2) AS truncated_value,
    TRUNC(-8.789::numeric, 2) AS negative_truncated;

Output

rounded_value | truncated_value | negative_truncated --------------+-----------------+------------------ 8.79 | 8.78 | -8.78

TRUNC removes digits after the requested precision. For negative numbers, it truncates toward zero rather than rounding toward negative infinity.

5. ROUND and TRUNC with Negative Precision

ROUND and TRUNC can also operate on digits to the left of the decimal point.

For example:

- ROUND(146, -1) rounds to the nearest ten.
- TRUNC(146, -1) removes the ones digit.
- ROUND(146, -2) rounds to the nearest hundred.
- TRUNC(146, -2) removes the tens and ones digits.

Example:

Negative Precision

sql

1
2
3
4
5
SELECT
    ROUND(146::numeric, -1) AS rounded_ten,
    TRUNC(146::numeric, -1) AS truncated_ten,
    ROUND(146::numeric, -2) AS rounded_hundred,
    TRUNC(146::numeric, -2) AS truncated_hundred;

Output

rounded_ten | truncated_ten | rounded_hundred | truncated_hundred ------------+---------------+-----------------+------------------ 150 | 140 | 100 | 100

TRUNC discards the specified digits. ROUND considers the next digit when determining the rounded result.

6. CEIL: Rounding Up to an Integer

CEIL, also written CEILING, returns the smallest integer greater than or equal to the input.

Syntax:

CEIL(number)
CEILING(number)

Examples:

- CEIL(4.2) returns 5.
- CEIL(4.0) returns 4.
- CEIL(-4.2) returns -4.

Important: CEIL does not simply increase every number by one. If the input is already an integer, it remains unchanged.

Using CEIL

sql

1
2
3
4
SELECT
    CEIL(4.2) AS positive_decimal,
    CEIL(4.0) AS whole_number,
    CEIL(-4.2) AS negative_decimal;

Output

positive_decimal | whole_number | negative_decimal -----------------+--------------+---------------- 5 | 4 | -4

CEIL returns the smallest integer that is not less than the input value.

7. FLOOR: Rounding Down to an Integer

FLOOR returns the largest integer less than or equal to the input.

Syntax:

FLOOR(number)

Examples:

- FLOOR(4.8) returns 4.
- FLOOR(4.0) returns 4.
- FLOOR(-4.2) returns -5.

A common misunderstanding is that FLOOR always removes the decimal part. That is not correct for negative values. FLOOR moves toward negative infinity, while TRUNC moves toward zero.

FLOOR Versus TRUNC

sql

1
2
3
4
SELECT
    FLOOR(4.8) AS positive_floor,
    FLOOR(-4.2) AS negative_floor,
    TRUNC(-4.2::numeric) AS negative_trunc;

Output

positive_floor | negative_floor | negative_trunc ---------------+----------------+-------------- 4 | -5 | -4

For negative non-integers, FLOOR and TRUNC produce different results because they follow different rules.

8. CEIL Versus FLOOR

CEIL and FLOOR are useful when converting a fractional quantity into an integer bound.

| Input | CEIL | FLOOR |
|---:|---:|---:|
| 3.2 | 4 | 3 |
| 3.0 | 3 | 3 |
| -3.2 | -3 | -4 |
| -3.0 | -3 | -3 |

Example: If 23 people need transport and each vehicle can carry 5 people, CEIL(23.0 / 5) returns 5 vehicles. FLOOR(23.0 / 5) returns 4, which is not enough for everyone.

Use CEIL when every partial group needs a full unit. Use FLOOR when only complete units count.

Calculating Required Vehicles

sql

1
SELECT CEIL(23.0 / 5) AS vehicles_required;

Output

vehicles_required ----------------- 5

Five vehicles are required because four vehicles can carry only 20 people.

9. ABS: Absolute Value

ABS returns the absolute value of a number. The absolute value represents the number's distance from zero and is never negative.

Syntax:

ABS(number)

Examples:

- ABS(7) returns 7.
- ABS(-7) returns 7.
- ABS(0) returns 0.

ABS is useful for calculating the size of a difference without caring about its sign, such as the difference between an expected amount and an actual amount.

Using ABS

sql

1
2
3
4
SELECT
    ABS(-15) AS positive_value,
    ABS(15) AS unchanged_value,
    ABS(0) AS zero_value;

Output

positive_value | unchanged_value | zero_value ---------------+-----------------+----------- 15 | 15 | 0

ABS converts a negative number to its non-negative magnitude. It does not change an already non-negative value.

10. Practical Example: Finding Large Differences

Suppose an inventory system stores expected and actual stock counts. ABS can calculate the magnitude of the discrepancy.

The following query assumes an inventory table with expected_stock and actual_stock columns.

Inventory Discrepancy

sql

1
2
3
4
5
6
7
SELECT
    product_id,
    expected_stock,
    actual_stock,
    ABS(expected_stock - actual_stock) AS stock_difference
FROM inventory
ORDER BY stock_difference DESC;

Output

Illustrative output: product_id | expected_stock | actual_stock | stock_difference -----------+----------------+--------------+----------------- 101 | 100 | 92 | 8 102 | 45 | 49 | 4 103 | 30 | 30 | 0

The query calculates the absolute difference for each product and sorts the largest discrepancies first. The output is illustrative; the inventory table must exist with the referenced columns.

11. MOD: Finding the Remainder

MOD returns the remainder after dividing one number by another.

Syntax:

MOD(dividend, divisor)

For example, MOD(10, 3) returns 1 because 10 divided by 3 leaves a remainder of 1.

Common uses include:

- Checking whether an integer is even or odd.
- Assigning records to repeating groups.
- Finding the remainder after division.

A divisor of zero causes a division-by-zero error. Do not use zero as the second argument.

Using MOD

sql

1
2
3
4
SELECT
    MOD(10, 3) AS remainder,
    MOD(12, 3) AS divisible_remainder,
    MOD(11, 2) AS odd_remainder;

Output

remainder | divisible_remainder | odd_remainder ----------+---------------------+-------------- 1 | 0 | 1

A zero remainder means the dividend is evenly divisible by the divisor.

12. Practical Example: Even and Odd Numbers

For integers, MOD(number, 2) returns zero when the number is even and a non-zero remainder when it is odd.

This example assumes a table named numbers with an integer column named value.

Classifying Numbers

sql

1
2
3
4
5
6
7
8
SELECT
    value,
    CASE
        WHEN MOD(value, 2) = 0 THEN 'Even'
        ELSE 'Odd'
    END AS number_type
FROM numbers
ORDER BY value;

Output

CASE selects the label based on the remainder. This example assumes the value column contains integers and is not NULL.

13. POWER: Exponentiation

POWER raises a number to a specified exponent.

Syntax:

POWER(base, exponent)

Examples:

- POWER(2, 3) returns 8 because 2 × 2 × 2 = 8.
- POWER(5, 2) returns 25.
- POWER(9, 0.5) returns approximately 3.

POWER is useful for mathematical models, growth calculations, and scientific data processing.

The result type and valid input combinations depend on the argument types. Negative bases with non-integer exponents do not produce ordinary real-valued results in PostgreSQL.

Using POWER

sql

1
2
3
4
SELECT
    POWER(2, 3) AS two_cubed,
    POWER(5, 2) AS five_squared,
    POWER(9, 0.5) AS square_root_of_nine;

Output

two_cubed | five_squared | square_root_of_nine ----------+--------------+------------------- 8 | 25 | 3

POWER calculates the base raised to the exponent. The square-root result is mathematically 3; the displayed representation may depend on the result type and client.

14. Combining Numeric Functions

You can nest numeric functions to perform multiple operations in one expression.

For example:

ROUND(ABS(-12.678), 2)

Step by step:

1. ABS(-12.678) returns 12.678.
2. ROUND(12.678, 2) returns 12.68.

SQL evaluates the inner expression to produce the argument for the outer function.

Combining ABS and ROUND

sql

1
SELECT ROUND(ABS(-12.678)::numeric, 2) AS result;

Output

result ------ 12.68

ABS removes the negative sign, and ROUND limits the result to two decimal places.

15. Real-World Example: Product Pricing

An online store may store product prices and discount percentages. Numeric expressions can calculate the discount amount and final price.

This example assumes a products table with product_id, price, and discount_percent columns. It uses NUMERIC values for decimal arithmetic.

Calculating Discounted Prices

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    product_id,
    price,
    discount_percent,
    ROUND(
        (price * discount_percent / 100)::numeric,
        2
    ) AS discount_amount,
    ROUND(
        (price - price * discount_percent / 100)::numeric,
        2
    ) AS final_price
FROM products
ORDER BY product_id;

Output

Illustrative output: product_id | price | discount_percent | discount_amount | final_price -----------+--------+------------------+-----------------+----------- 1 | 100.00 | 10 | 10.00 | 90.00 2 | 249.99 | 15 | 37.50 | 212.49

The query calculates the discount and final price. The results are illustrative. For financial systems, define a consistent rounding policy and store monetary values using an appropriate exact numeric type.

16. NULL Values and Numeric Functions

NULL represents a missing or unknown value. Numeric functions generally return NULL when a required numeric argument is NULL.

Examples:

- ROUND(NULL::numeric, 2) returns NULL.
- ABS(NULL::numeric) returns NULL.
- POWER(NULL::numeric, 2) returns NULL.
- MOD(10, NULL::integer) returns NULL.

A NULL result is different from zero. Zero is a known numeric value, while NULL means the value is missing or unknown.

Use COALESCE only when replacing missing data with a specific default is logically correct. Do not automatically convert every NULL to zero.

Handling NULL

sql

1
2
3
4
SELECT
    ROUND(NULL::numeric, 2) AS rounded_missing,
    ABS(NULL::numeric) AS absolute_missing,
    COALESCE(ROUND(NULL::numeric, 2), 0) AS defaulted_value;

Output

rounded_missing | absolute_missing | defaulted_value ----------------+------------------+---------------- NULL | NULL | 0

The first two expressions remain NULL. COALESCE explicitly substitutes zero in the third expression; use this only if zero is a valid business default.

17. Numeric Precision and Financial Calculations

PostgreSQL provides several numeric types, and their behavior matters when performing calculations.

NUMERIC / DECIMAL:
- Exact decimal arithmetic within the type's precision limits.
- Often appropriate for financial amounts.

REAL / DOUBLE PRECISION:
- Approximate floating-point types.
- Some decimal fractions cannot be represented exactly in binary floating-point.

INTEGER / BIGINT:
- Whole-number types.
- Useful for counts and other integral values.

Example:

SELECT ROUND(2.675::numeric, 2);

For NUMERIC, this returns 2.68 in PostgreSQL.

Do not assume that every floating-point expression produces the same result as an exact NUMERIC expression. Casting an already approximate value to NUMERIC cannot restore precision that was previously lost.

Rounding policy also matters: decide whether to round each line item, each tax component, or only the final total. These choices can produce different totals.

18. Numeric Functions and WHERE Clauses

Numeric functions can appear in WHERE conditions, but applying a function to an indexed column can sometimes prevent a regular index on that column from being used efficiently.

For example, if price has a normal index, this expression may be less index-friendly:

WHERE ROUND(price, 0) = 100

If the requirement is to find prices in a range, consider expressing the condition as a range when that is logically equivalent.

For exact equivalence, be careful: rounding rules and boundary values affect which rows match. Always verify the rewritten condition before using it.

Filter by a Price Range

sql

1
2
3
4
SELECT product_id, price
FROM products
WHERE price >= 99.5
  AND price < 100.5;

Output

Illustrative output: product_id | price -----------+------ 1 | 100.00

This query filters by a range rather than applying ROUND to each price. The example assumes the desired business rule is the specified range; it is not universally equivalent to ROUND(price, 0) = 100.

19. Common Numeric Function Patterns

| Requirement | Function or expression | Example |
|---|---|---|
| Round to two decimal places | ROUND | ROUND(12.345::numeric, 2) |
| Remove extra decimal digits | TRUNC | TRUNC(12.345::numeric, 2) |
| Round upward to an integer bound | CEIL | CEIL(4.2) |
| Round downward to an integer bound | FLOOR | FLOOR(4.8) |
| Remove a negative sign | ABS | ABS(-15) |
| Find a remainder | MOD | MOD(10, 3) |
| Calculate an exponent | POWER | POWER(2, 3) |
| Replace a missing result deliberately | COALESCE | COALESCE(ROUND(value, 2), 0) |

Choose the function that matches the actual business rule. Similar-looking functions can produce different results, particularly for negative values and rounding boundaries.

20. Practice: Predict the Output

Try to predict each result before checking the answers.

Question 1:
SELECT ROUND(7.846::numeric, 2);

Question 2:
SELECT TRUNC(7.846::numeric, 2);

Question 3:
SELECT CEIL(-2.1);

Question 4:
SELECT FLOOR(-2.1);

Question 5:
SELECT ABS(-19);

Question 6:
SELECT MOD(17, 5);

Question 7:
SELECT POWER(3, 3);

Question 8:
SELECT ROUND(149::numeric, -1);

Question 9:
SELECT TRUNC(149::numeric, -1);

Question 10:
SELECT ROUND(NULL::numeric, 2);

21. Practice Answers and Explanations

1. ROUND(7.846::numeric, 2) returns 7.85.
The third decimal digit is 6, so the second decimal digit rounds upward.

2. TRUNC(7.846::numeric, 2) returns 7.84.
TRUNC removes the remaining decimal digits without rounding.

3. CEIL(-2.1) returns -2.
-2 is the smallest integer greater than or equal to -2.1.

4. FLOOR(-2.1) returns -3.
-3 is the largest integer less than or equal to -2.1.

5. ABS(-19) returns 19.

6. MOD(17, 5) returns 2.

7. POWER(3, 3) returns 27.

8. ROUND(149::numeric, -1) returns 150.

9. TRUNC(149::numeric, -1) returns 140.

10. ROUND(NULL::numeric, 2) returns NULL.

22. Guided Exercise: Calculate Package Counts

Scenario:

A warehouse has 127 items. Each box can hold 12 items. Every item must be packed.

Task:
Write a query that calculates how many boxes are required.

Hint:
Divide the total number of items by the capacity of each box, then use CEIL.

Expected approach:
CEIL(127.0 / 12)

Try writing the query yourself before viewing the solution.

23. Guided Exercise Solution

Solution:

SELECT CEIL(127.0 / 12) AS boxes_required;

Result: 11

Explanation:
10 boxes hold 120 items, leaving 7 items. An additional box is required, so the total is 11 boxes.

Using FLOOR would return 10 and would not provide enough capacity.

24. Multiple-Choice Quiz

Choose the best answer for each question. The answers and explanations are included below.

25. Interview Questions and Answers

Q1. What are numeric functions in SQL?
Answer: Functions that perform mathematical calculations or transformations on numeric values.

Q2. What is ROUND used for?
Answer: ROUND rounds a numeric value to a specified decimal precision.

Q3. What is the difference between ROUND and TRUNC?
Answer: ROUND applies rounding rules, while TRUNC removes digits beyond the requested precision without rounding.

Q4. What does CEIL return?
Answer: The smallest integer greater than or equal to its input.

Q5. What does FLOOR return?
Answer: The largest integer less than or equal to its input.

Q6. How do FLOOR and TRUNC differ for negative values?
Answer: FLOOR moves toward negative infinity; TRUNC moves toward zero. For example, FLOOR(-2.7) is -3, while TRUNC(-2.7) is -2.

Q7. What does ABS do?
Answer: It returns the absolute value of a number.

Q8. What does MOD do?
Answer: It returns the remainder after division.

Q9. How can MOD help identify even integers?
Answer: An integer is even when MOD(value, 2) = 0.

Q10. What does POWER do?
Answer: It raises a base value to an exponent.

Q11. Can ROUND use a negative precision in PostgreSQL?
Answer: Yes. A negative precision rounds digits to the left of the decimal point for NUMERIC values.

Q12. Can TRUNC use a negative precision in PostgreSQL?
Answer: Yes. It removes digits to the left of the decimal point according to the specified precision.

Q13. What happens when a numeric function receives NULL?
Answer: Numeric functions generally return NULL when a required numeric argument is NULL.

Q14. Why is NUMERIC often used for financial calculations?
Answer: It provides exact decimal arithmetic within the chosen precision, unlike approximate floating-point types.

Q15. Why might floating-point calculations surprise developers?
Answer: Some decimal fractions cannot be represented exactly in binary floating-point, which can lead to small representation differences.

Q16. What is the difference between CEIL(4.2) and FLOOR(4.2)?
Answer: CEIL returns 5; FLOOR returns 4.

Q17. Why should MOD not use zero as its divisor?
Answer: Division by zero raises an error.

Q18. Can numeric functions be used inside SELECT?
Answer: Yes. They can transform values directly in a SELECT expression.

Q19. Can a numeric function be used in WHERE?
Answer: Yes, but applying a function to an indexed column can affect index usage. Check the execution plan and consider equivalent range conditions where appropriate.

Q20. Does using a numeric function guarantee a faster query?
Answer: No. Query performance depends on the query, data distribution, indexes, and execution plan.

Q21. How would you calculate the number of boxes needed for 127 items if each box holds 12?
Answer: CEIL(127.0 / 12), which returns 11.

Q22. What is the difference between ROUND(149::numeric, -1) and TRUNC(149::numeric, -1)?
Answer: ROUND returns 150, while TRUNC returns 140.

Q23. Is ROUND always the right choice for monetary calculations?
Answer: Not automatically. The correct rounding stage and policy depend on the application's financial rules.

Q24. Can POWER produce a fractional result?
Answer: Yes. For example, POWER(9, 0.5) calculates the square root of 9.

Q25. What should you check before deploying a numeric calculation?
Answer: Input types, NULL handling, division-by-zero cases, precision, rounding policy, expected boundaries, and query performance where relevant.

26. Lesson Summary

In this lesson, you learned how to use PostgreSQL numeric functions:

- ROUND rounds a number to a requested precision.
- TRUNC removes digits without rounding.
- CEIL returns the smallest integer greater than or equal to a number.
- FLOOR returns the largest integer less than or equal to a number.
- ABS returns the absolute value.
- MOD returns a remainder.
- POWER calculates an exponent.
- NULL inputs generally produce NULL results.
- NUMERIC is useful for exact decimal financial calculations.
- Numeric functions can be combined inside SELECT expressions and other SQL clauses.

The most important distinction to remember is that ROUND, TRUNC, CEIL, and FLOOR do different jobs. Test positive values, negative values, integers, decimal boundaries, and NULLs to understand their behavior.

27. Common Mistakes

Avoid these frequent errors when using numeric functions.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

ROUND changes a value to the requested decimal precision using rounding.
TRUNC removes digits beyond the requested precision without rounding.
CEIL returns the smallest integer greater than or equal to a number.
FLOOR returns the largest integer less than or equal to a number.
ABS returns the absolute value, MOD returns a remainder, and POWER calculates an exponent.
Numeric functions generally return NULL when their numeric argument is NULL.
Use NUMERIC for exact decimal calculations such as financial amounts.
Test edge cases and verify the behavior of numeric expressions before using them in production.

Was this lesson helpful?

Found an error? Report an issue