Numeric Functions in SQL: Rounding, Modulo, Power, and Square Root

SQL isn’t just about text and tables. In real databases, you often have to deal with prices, quantities, points, percentages, coordinates—in other words, numbers. Imagine this:

• You’re calculating discounts and want to round the amount to a whole number.
• You want to show data for even/odd students.
• You’re doing analytics on grades and want to know the square root of the variance.
• Or you’re building score calculation formulas with exponents.

SQL can handle all this without any help from outside programming languages—you can do it right in your queries using built-in math functions. Today, we’ll break down six of the most useful ones:

• ROUND() — rounding
• CEIL() — round up
• FLOOR() — round down
• MOD() — modulo (remainder)
• POWER() — exponentiation
• SQRT() — square root

* Rounding Numbers: ROUND(), CEIL(), FLOOR()*

When you’re working with decimals (like 4.67891), you often need to round the result. For example, if you’re calculating an average grade, total order price, discount percentage, etc.—extra digits after the decimal point not only look messy, but can also be confusing.

ROUND() — regular math rounding

ROUND(number [, digits])

• number — the number you want to round.
• digits (optional) — how many digits to keep after the decimal point.

Examples:

SELECT ROUND(4.67);       -- 5
SELECT ROUND(4.6789, 2);  -- 4.68

ROUND() works just like the rounding you know from math: 4.5 → 5, 4.49 → 4.

Super useful if you’re working with money or points that need to look nice: 4.33333 → 4.33.

CEIL() — round up to the nearest integer

SELECT CEIL(4.1);  -- 5

• If the number is already an integer, the result doesn’t change.
• Always returns an integer, not less than the original.

Handy, for example, if you need to figure out how many pages to show products: 21 products with 10 per page → you need 3 pages.

FLOOR() — round down

SELECT FLOOR(4.9);  -- 4

• Returns an integer not greater than the original.

Use it when you need to figure out the “floor” or “step” where a value is at.

Comparison:

* Modulo: MOD()*

Why do you need the remainder? For example:

• Check if a number is even.
• Split rows into groups by a pattern (like splitting into 3 teams).
• Implement a repeating cycle (like “every fifth record”).

Syntax:

MOD(dividend, divisor)

Examples:

SELECT MOD(17, 5);  -- 2  (3*5 +2)
SELECT MOD(10, 3);  -- 1  (3*3 +1)

Heads up: the sign of the remainder depends on the sign of the first argument (dividend).

Usage:

SELECT student_id,
       CASE WHEN MOD(student_id, 2) = 0 THEN 'Even' ELSE 'Odd' END AS parity
FROM students;

* Exponentiation: POWER()*

Sometimes you need more than just multiplication—you need to use math formulas:

• Calculating interest: base * POWER(1 + rate, years)
• Area of a circle: π * r^2
• Formula weights in machine learning

Syntax:

POWER(base, exponent)

Examples:

SELECT POWER(2, 3);   -- 8
SELECT POWER(5, 2);   -- 25
SELECT POWER(9, 0.5); -- 3 (square root)

This function takes any numbers: integers, decimals, negatives.

Square Root: SQRT()

If you need to get a root, especially in stats (like standard deviation), SQRT() is your friend.

SELECT SQRT(25);    -- 5
SELECT SQRT(2);     -- ~1.4142

If you pass a negative number—you’ll get an error. If that’s possible in your data, use ABS():

SELECT SQRT(ABS(-25));  -- 5

Practical Scenarios

Scenario 1: rounding the final order total

SELECT order_id, ROUND(total_price, 0) AS total_rounded
FROM orders;

Scenario 2: figuring out how many pages to show

SELECT CEIL(COUNT(*) / 10.0) AS pages_needed
FROM products;

Scenario 3: splitting students into 3 groups

SELECT student_id,
       MOD(student_id, 3) AS group_number
FROM students;

Scenario 4: root mean square

SELECT SQRT(AVG(POWER(score, 2))) AS root_mean_square
FROM grades;

Common Mistakes and Tips

ROUND() can take a second argument—don’t forget it if you need to round to hundredths.

MOD() with negative numbers can give you weird results.

POWER() and SQRT() work with decimals—use CAST() if you need to.

Make sure you don’t pass a negative number to SQRT(), or you’ll get a runtime error.