ABS / MOD / POWER

ABS: Returns the absolute value.

ABS(number)

MOD: Returns the remainder (modulo).

MOD(number, divisor)
number % divisor   -- Operator syntax

POWER: Returns the power of a number.

POWER(base, exponent)
POW(base, exponent)   -- Alias in MySQL

SQRT: Returns the square root.

SQRT(number)

SIGN: Returns the sign of a number (positive: 1, zero: 0, negative: -1).

SIGN(number)

The following examples use the shop_sales table.

Compare the difference from the sales target using absolute values.

sample_abs_mod_power.sql

SELECT
    shop_name,
    sales,
    target,
    sales - target AS diff,
    ABS(sales - target) AS abs_diff,
    SIGN(sales - target) AS achieved  -- 1:exceeded 0:exact -1:missed
FROM shop_sales;

+------------------+---------+---------+---------+----------+----------+
| shop_name        | sales   | target  | diff    | abs_diff | achieved |
+------------------+---------+---------+---------+----------+----------+
| org_a Main Store | 3200000 | 3000000 |  200000 |   200000 |        1 |
| store_b          | 2600000 | 2800000 | -200000 |   200000 |       -1 |
| store_c          | 2850000 | 2850000 |       0 |        0 |        0 |
+------------------+---------+---------+---------+----------+----------+
3 rows in set

Check whether each store's sales are a multiple of 1,000,000 (MOD).

sample_abs_mod_power.sql

SELECT
    shop_name,
    sales,
    CASE MOD(sales, 1000000)
        WHEN 0 THEN 'exact multiple'
        ELSE 'has remainder'
    END AS category
FROM shop_sales;

+------------------+---------+---------------+
| shop_name        | sales   | category      |
+------------------+---------+---------------+
| org_a Main Store | 3200000 | has remainder |
| store_b          | 2600000 | has remainder |
| store_c          | 2850000 | has remainder |
+------------------+---------+---------------+
3 rows in set

Calculate the 5-year forecast assuming 5% annual growth from the target amount (POWER).

sample_abs_mod_power.sql

SELECT
    shop_name,
    target,
    ROUND(target * POWER(1.05, 5), 0) AS forecast_5yr
FROM shop_sales;

+------------------+---------+--------------+
| shop_name        | target  | forecast_5yr |
+------------------+---------+--------------+
| org_a Main Store | 3000000 |      3828845 |
| store_b          | 2800000 |      3573588 |
| store_c          | 2850000 |      3637402 |
+------------------+---------+--------------+
3 rows in set

Calculate the square root of each store's sales amount (SQRT).

sample_abs_mod_power.sql

SELECT
    shop_name,
    sales,
    ROUND(SQRT(sales), 2) AS sqrt_sales
FROM shop_sales;

+------------------+---------+------------+
| shop_name        | sales   | sqrt_sales |
+------------------+---------+------------+
| org_a Main Store | 3200000 |    1788.85 |
| store_b          | 2600000 |    1612.45 |
| store_c          | 2850000 |    1688.19 |
+------------------+---------+------------+
3 rows in set

MySQL / MariaDB

All of ABS, POWER (alias POW), SQRT, SIGN, and MOD are available. The % operator is also supported.

SELECT ABS(sales - target) AS abs_diff FROM shop_sales;
SELECT POWER(1.05, 5) AS growth_factor;
SELECT MOD(sales, 1000000) AS remainder FROM shop_sales;

PostgreSQL

ABS, POWER, SQRT, SIGN, and MOD are all available. The MySQL alias POW is not supported in PostgreSQL — use POWER instead.

SELECT ABS(sales - target) AS abs_diff FROM shop_sales;
SELECT POWER(1.05, 5) AS growth_factor;
SELECT MOD(sales, 1000000) AS remainder FROM shop_sales;

SQLite

In SQLite, ABS and ROUND are available, but POWER, SQRT, and SIGN are not supported by default. If these are needed, calculate them in the application layer or use an extension library. Use the % operator for remainders.

SELECT ABS(sales - target) AS abs_diff FROM shop_sales;
SELECT sales % 1000000 AS remainder FROM shop_sales;

Be careful with MOD: dividing by zero returns NULL in some databases (such as MySQL) or causes an error depending on the DBMS. It is commonly used for odd/even checks and grouping by periodic cycles.

SIGN returns one of three numeric values — positive, zero, or negative — instead of using a CASE expression, which can make certain aggregations more concise, such as counting stores that met their target, compared to COUNT(CASE WHEN ... END).

Passing a negative number to SQRT returns NULL in MySQL but raises an error in PostgreSQL — behavior differs by DBMS. For rounding numbers, see ROUND / CEIL / FLOOR.