SQL Academy · Lesson

Useful Math Functions

ABS, MOD, POWER and more.

Useful Math Functions is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Math Functions Matter

SQL is not just about storing and retrieving text — it is also a powerful tool for numeric calculations. Built-in math functions let you compute absolute values, remainders, powers, and more, all without leaving your query.

In this lesson you will learn the most useful numeric functions in SQL: ABS, MOD, POWER, SQRT, ROUND, CEIL, FLOOR, and SIGN.

ABS — Absolute Value

ABS(n) returns the absolute (non-negative) value of a number. Negative numbers become positive; positive numbers and zero are unchanged.

This is handy when you care about the distance from zero rather than the direction — for example, calculating how far a temperature is from the target, regardless of whether it is above or below.

SELECT ABS(-42),  -- 42
       ABS(15),   -- 15
       ABS(0);    -- 0

ABS in a Real Query

Imagine a transactions table where withdrawals are stored as negative amounts. You can use ABS to display every amount as a positive number regardless of direction.

CREATE TABLE transactions (
  id     INTEGER,
  amount NUMERIC
);

INSERT INTO transactions VALUES (1, 200.00), (2, -50.00), (3, -120.00), (4, 80.00);

SELECT id,
       amount,
       ABS(amount) AS absolute_amount
FROM   transactions;

MOD — Remainder After Division

MOD(dividend, divisor) returns the remainder when the first number is divided by the second. It is the SQL equivalent of the % operator in many programming languages.

A classic use case is checking whether a number is even or odd: if MOD(n, 2) = 0 the number is even, otherwise it is odd.

SELECT MOD(10, 3),  -- 1  (10 = 3*3 + 1)
       MOD(15, 5),  -- 0  (15 = 5*3 + 0)
       MOD(7,  2);  -- 1  (odd number)

MOD in a Real Query

You can use MOD inside a WHERE clause to filter rows. Here we select only the orders with an even id.

CREATE TABLE orders (
  id    INTEGER,
  total NUMERIC
);

INSERT INTO orders VALUES (1, 99.99), (2, 45.00), (3, 130.50), (4, 22.00), (5, 75.00), (6, 60.00);

SELECT id, total
FROM   orders
WHERE  MOD(id, 2) = 0;

POWER — Raise to an Exponent

POWER(base, exponent) raises a number to the given power. POWER(2, 10) gives 1024, and POWER(9, 0.5) gives 3 (the square root of 9).

This function is useful for compound-interest calculations, area formulas, and any situation that involves exponential growth.

SELECT POWER(2, 10),   -- 1024
       POWER(3, 3),    -- 27
       POWER(9, 0.5);  -- 3 (square root)

SQRT — Square Root

SQRT(n) is a convenient shorthand for POWER(n, 0.5). It returns the square root of a non-negative number.

For example, if you store the area of a square plot of land you can recover the side length with SQRT(area).

SELECT SQRT(25),   -- 5
       SQRT(2),    -- 1.4142...
       SQRT(100);  -- 10

ROUND — Round to N Decimal Places

ROUND(n, d) rounds n to d decimal places. If you omit d (or pass 0) the result is an integer. Negative values of d round to the left of the decimal point.

SELECT ROUND(3.14159, 2),  -- 3.14
       ROUND(2.555, 2),    -- 2.56
       ROUND(1234.5, -2),  -- 1200
       ROUND(9.9);         -- 10

CEIL and FLOOR — Always Round Up or Down

CEIL(n) (also written CEILING) always rounds up to the nearest integer, while FLOOR(n) always rounds down.

Use CEIL when you need a minimum number of something (e.g., pages needed to print rows) and FLOOR when you need the largest whole number that does not exceed a value (e.g., complete weeks elapsed).

SELECT CEIL(4.1),   -- 5
       CEIL(-4.1),  -- -4
       FLOOR(4.9),  -- 4
       FLOOR(-4.9); -- -5

SIGN — What Direction Is the Number?

SIGN(n) returns 1 if the number is positive, -1 if it is negative, and 0 if it is zero. It is a quick way to categorise numeric values without writing a CASE expression.

SELECT SIGN(42),   -- 1
       SIGN(-7),   -- -1
       SIGN(0);    -- 0

Combining Math Functions

Math functions can be combined freely inside a single expression. The example below calculates the monthly payment for a simple loan using ROUND and POWER together, showing how these functions complement each other in real business logic.

-- Monthly payment formula: P * r / (1 - (1+r)^-n)
-- P=10000, annual rate 6% (r=0.005/month), n=24 months
SELECT ROUND(
  10000.0 * 0.005 / (1 - POWER(1 + 0.005, -24)),
  2
) AS monthly_payment;

Quick Check

Test your understanding of SQL math functions.

Lesson Recap

Great work! Here is a summary of the SQL math functions you learned in this lesson:

ABS(n) — absolute value (removes the sign)
MOD(a, b) — remainder after dividing a by b
POWER(base, exp) — raises a number to a power
SQRT(n) — square root
ROUND(n, d) — rounds to d decimal places
CEIL(n) — always rounds up to the next integer
FLOOR(n) — always rounds down to the previous integer
SIGN(n) — returns 1, -1, or 0 based on the sign of the number

These functions work in all major SQL databases and can be nested or combined to handle complex numeric logic directly in your queries.

Frequently asked questions

Is the “Useful Math Functions” lesson free?

Yes — the full text of “Useful Math Functions” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Useful Math Functions”?

ABS, MOD, POWER and more. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Useful Math Functions” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy