SQL Numeric Functions

SQL provides a set of built-in numeric functions for performing mathematical operations on number values. These functions can round numbers, find absolute values, calculate remainders, generate random numbers, and more. They can be used directly in SELECT statements or as part of calculations in queries.

The Reference Table

1. ROUND()

ROUND(number, decimal_places) rounds a number to the specified number of decimal places. If the decimal places argument is omitted, it rounds to the nearest whole number.

SELECT StudentName, Score,
       ROUND(Score, 1)  AS Rounded1,
       ROUND(Score, 0)  AS RoundedWhole,
       ROUND(Score, -1) AS RoundedTens
FROM Students;

Result:

A negative decimal places value rounds to the left of the decimal point — ROUND(78.65, -1) rounds to the nearest 10, giving 80.

2. CEIL() / CEILING()

CEIL(number) rounds a number up to the nearest whole number, even if the decimal part is very small. Both CEIL and CEILING work the same way.

SELECT StudentName, Score,
       CEIL(Score) AS CeilValue
FROM Students;

Result:

CEIL(91.30) gives 92 — it rounds up even though the decimal is only .30.

3. FLOOR()

FLOOR(number) rounds a number down to the nearest whole number, always dropping the decimal portion regardless of its value.

SELECT StudentName, Score, FLOOR(Score) AS FloorValue
FROM Students;

Result:

ROUND vs CEIL vs FLOOR

4. ABS()

ABS(number) returns the absolute value — the positive version of any number. Negative values become positive; positive values remain unchanged.

SELECT StudentName, Score, ABS(Score) AS AbsoluteScore
FROM Students;

Result for Rohit Das: Score = -10.00, ABS = 10.00

-- Practical use: Find the difference between two scores regardless of order
SELECT ABS(Score - 85) AS Deviation FROM Students;

5. MOD()

MOD(dividend, divisor) returns the remainder after dividing one number by another. This is also written as dividend % divisor in some databases.

SELECT StudentID, MOD(StudentID, 2) AS IsEven
FROM Students;

Result:

-- Select only even-numbered students
SELECT * FROM Students WHERE MOD(StudentID, 2) = 0;

6. POWER() / POW()

POWER(base, exponent) raises a number to the power of another number.

SELECT POWER(2, 10) AS TwoToTheTen;   -- Result: 1024
SELECT POWER(3, 3)  AS ThreeCubed;    -- Result: 27

7. SQRT()

SQRT(number) returns the square root of a number.

SELECT SQRT(144) AS SquareRoot;   -- Result: 12
SELECT SQRT(Score) AS SqrtScore FROM Students;

8. TRUNCATE()

TRUNCATE(number, decimal_places) cuts off the decimal part at the specified position without rounding. It simply removes the digits beyond the specified decimal places.

SELECT Fees, TRUNCATE(Fees, 0) AS TruncatedFees
FROM Students;

Result:

Unlike ROUND(), TRUNCATE(38000.75, 0) gives 38000 — not 38001. It simply removes the .75.

9. RAND()

RAND() generates a random decimal number between 0 and 1. It is commonly used to retrieve random rows from a table.

-- Get a random decimal between 0 and 1
SELECT RAND();

-- Get a random integer between 1 and 100
SELECT FLOOR(RAND() * 100) + 1 AS RandomNumber;

-- Retrieve 2 random students from the table
SELECT StudentName FROM Students ORDER BY RAND() LIMIT 2;

10. SIGN()

SIGN(number) returns -1 if the number is negative, 0 if it is zero, and 1 if it is positive.

SELECT Score, SIGN(Score) AS ScoreSign
FROM Students;

Result for Rohit Das: Score = -10.00, SIGN = -1

Quick Reference Table

Key Points to Remember

• Numeric functions work on both literal numbers and column values.
• ROUND() uses standard rounding rules; TRUNCATE() simply removes the digits — no rounding.
• CEIL() always rounds up; FLOOR() always rounds down.
• RAND() returns a different value every time the query runs — it is not deterministic.
• Numeric functions do not modify the actual data in the table — they only affect the query output.

Summary

SQL numeric functions perform mathematical operations on number values within queries. From basic rounding with ROUND(), CEIL(), and FLOOR(), to absolute values with ABS(), remainders with MOD(), and randomisation with RAND() — these functions are practical tools for data transformation and analysis directly inside SQL queries.