Mathematical Functions in SQL: ROUND(), CEIL(), FLOOR(), MOD() and More

Table of Contents

1. Introduction
2. Why Mathematical Functions Matter in SQL
3. Overview of Common SQL Math Functions
4. ROUND() – Round Numbers
5. CEIL() or CEILING() – Round Up
6. FLOOR() – Round Down
7. MOD() – Modulo (Remainder)
8. ABS() – Absolute Value
9. POWER() – Raise to Power
10. SQRT() – Square Root
11. EXP() – Exponential
12. LOG() and LN() – Logarithmic Values
13. PI() – Return Value of π
14. RAND() – Generate Random Number
15. SIGN() – Determine Sign of a Number
16. TRUNC() – Truncate Decimal Digits (DBMS Specific)
17. Math Function Nesting (Using Multiple Together)
18. Real-World Use Case: Financial Rounding
19. Best Practices for Numeric Calculations in SQL
20. Summary and What’s Next

1. Introduction

Mathematical functions in SQL enable you to perform numeric operations directly inside queries. Whether you’re calculating tax, rounding prices, or generating random values for sampling, these functions are essential for data analysis and reporting.

2. Why Mathematical Functions Matter in SQL

SQL isn’t just about retrieving data — it’s also about transforming it. Math functions help:

• Clean and normalize numeric data
• Create financial calculations
• Perform statistical analysis
• Randomize records or simulate data

3. Overview of Common SQL Math Functions

4. ROUND() – Round Numbers

sqlCopyEditSELECT ROUND(123.4567, 2); -- Output: 123.46
SELECT ROUND(123.4567);    -- Output: 123

Syntax: ROUND(number, decimal_places)

5. CEIL() or CEILING() – Round Up

sqlCopyEditSELECT CEIL(4.2); -- Output: 5
SELECT CEILING(-1.3); -- Output: -1

Always rounds up to the next whole number.

6. FLOOR() – Round Down

sqlCopyEditSELECT FLOOR(4.9); -- Output: 4
SELECT FLOOR(-4.9); -- Output: -5

Always rounds down (toward negative infinity).

7. MOD() – Modulo (Remainder)

sqlCopyEditSELECT MOD(10, 3); -- Output: 1
SELECT MOD(-10, 3); -- Output: -1

Used to check divisibility, alternating logic, etc.

8. ABS() – Absolute Value

sqlCopyEditSELECT ABS(-123); -- Output: 123

Removes the sign from the number.

9. POWER() – Raise to Power

sqlCopyEditSELECT POWER(2, 3); -- Output: 8

Calculates base raised to exponent (like 2³).

10. SQRT() – Square Root

sqlCopyEditSELECT SQRT(25); -- Output: 5

May return NULL or error for negative inputs depending on DBMS.

11. EXP() – Exponential (e^x)

sqlCopyEditSELECT EXP(1); -- Output: ~2.718281828

Useful in scientific or statistical queries.

12. LOG() and LN() – Logarithmic Functions

sqlCopyEditSELECT LOG(100);    -- MySQL: base 10
SELECT LN(100);     -- Natural log (base e)

Logarithmic values are commonly used in scoring systems, ranking, and scientific calculations.

13. PI() – Value of π

sqlCopyEditSELECT PI(); -- Output: 3.14159265358979

Great for geometry-based calculations (e.g., circles, spheres).

14. RAND() – Generate Random Number

sqlCopyEditSELECT RAND(); -- Output: 0.739573 (random float between 0 and 1)

In PostgreSQL: use RANDOM()

sqlCopyEditSELECT RANDOM(); -- Output: random float [0,1)

You can generate a range:

sqlCopyEditSELECT FLOOR(RAND() * 100) AS random_0_to_99;

15. SIGN() – Determine Sign of a Number

sqlCopyEditSELECT SIGN(-10); -- Output: -1
SELECT SIGN(0);   -- Output: 0
SELECT SIGN(10);  -- Output: 1

Useful for categorizing positive, negative, or zero values.

16. TRUNC() – Truncate Decimal Digits

In Oracle and PostgreSQL:

sqlCopyEditSELECT TRUNC(123.4567, 2); -- Output: 123.45

Unlike ROUND(), TRUNC() chops off digits instead of rounding.

17. Math Function Nesting (Using Multiple Together)

sqlCopyEditSELECT ABS(ROUND(-12.345, 1)); -- Output: 12.3

Combining functions gives flexibility in formatting and logic.

18. Real-World Use Case: Financial Rounding

sqlCopyEditSELECT order_id, ROUND(total_amount * 0.18, 2) AS tax_amount
FROM orders;

Calculates 18% tax rounded to 2 decimal places.

19. Best Practices for Numeric Calculations in SQL

• Always round monetary values to the required precision
• Use ABS() to guard against negative entries when needed
• Prefer ROUND() over TRUNC() unless truncation is required
• Avoid using RAND() without limits in production (for reproducibility)
• Validate edge cases (e.g., SQRT() of negative, LOG(0)) to avoid errors

20. Summary and What’s Next

Mathematical functions bring power and precision to SQL. Whether you’re rounding prices, calculating scientific values, or generating random data, these functions make numeric manipulation seamless and efficient.