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)

-- Compare the difference from the sales target using absolute values.
SELECT
    store_name,
    sales_amount,
    target_amount,
    sales_amount - target_amount AS difference,
    ABS(sales_amount - target_amount) AS difference_abs,
    SIGN(sales_amount - target_amount) AS achievement  -- 1: exceeded, 0: exact, -1: not reached
FROM store_sales;

-- Split processing by odd/even order ID (MOD).
SELECT
    order_id,
    customer_name,
    CASE MOD(order_id, 2)
        WHEN 0 THEN 'Even ID'
        ELSE 'Odd ID'
    END AS id_category
FROM orders;

-- Calculate the accumulated amount after 5 years using compound interest (POWER).
SELECT
    customer_name,
    principal,
    ROUND(principal * POWER(1.03, 5), 0) AS after_5years_3pct_compound
FROM savings_plan;

-- Calculate the square root of the order count (SQRT).
SELECT
    category_name,
    COUNT(*) AS order_count,
    ROUND(SQRT(COUNT(*)), 2) AS sqrt_of_count
FROM orders
GROUP BY category_name;

-- Comparison of sales vs. target.
store_name   | sales_amount | target_amount | difference | difference_abs | achievement
-------------+--------------+---------------+------------+----------------+------------
Akihabara    | 3200000      | 3000000       | 200000     | 200000         | 1
Shinjuku     | 2600000      | 2800000       | -200000    | 200000         | -1
Namba        | 2850000      | 2850000       | 0          | 0              | 0

-- Accumulated amount after 5 years (principal: 1,000,000, 3% compound interest).
customer_name | principal | after_5years_3pct_compound
--------------+-----------+---------------------------
Taro Yamada   | 1000000   | 1159274
Hanako Suzuki | 2000000   | 2318548

The functions ABS, POWER, SQRT, and SIGN are available across MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

-- Syntax common to all databases.
SELECT ABS(sales_amount - target_amount) AS difference_abs FROM store_sales;
SELECT POWER(1.03, 5) AS compound_factor;

The MOD function is available in MySQL, PostgreSQL, and Oracle. SQL Server does not support MOD, so use the % operator instead.

-- MySQL / PostgreSQL / Oracle: MOD function is available.
SELECT MOD(order_id, 2) AS remainder FROM orders;

-- SQL Server: Use the % operator.
SELECT order_id % 2 AS remainder FROM orders;

The alias POW in MySQL is MySQL-specific. Use POWER in PostgreSQL, Oracle, and SQL Server.

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 SQL Server — behavior differs by DBMS. For rounding numbers, see ROUND / CEIL / FLOOR.