Compute Absolute Value and Remainder

Use `ABS` to strip the sign from a number and `MOD` (or the `%` operator) to get the integer remainder after division.

5/5 supported engines validation-green 2 examples 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find the absolute deviation of each temperature reading from a target

The target temperature is 20. Sensor 1 (18) is 2 below target: ABS(18 - 20) = ABS(-2) = 2. Sensor 2 (25) is 5 above: ABS(5) = 5. Sensor 3 (-3) is 23 below: ABS(-23) = 23. ABS always returns a non-negative value regardless of which direction the reading deviates.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE readings (id INT, temp INT);

INSERT INTO
  readings
VALUES
  (1, 18),
  (2, 25),
  (3, -3),
  (4, 22);

Validated query Shared across supported engines.

SQL

SELECT
  id,
  temp,
  ABS(temp - 20) AS deviation
FROM
  readings
ORDER BY
  id;

Expected result Returned rows for the shared example.

id	temp	deviation
1	18	2
2	25	5
3	-3	23
4	22	2

ABS syntax is identical across all engines.

Example 2

Label rows as even or odd using MOD

MOD(1, 2) = 1 (odd), MOD(2, 2) = 0 (even), MOD(3, 2) = 1 (odd), MOD(4, 2) = 0 (even). The CASE WHEN wraps the remainder check to return a readable label. SQL Server and SQLite do not have a MOD function but the % infix operator performs the same operation.

MySQL MariaDB PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE items (id INT, name VARCHAR(50));

INSERT INTO
  items
VALUES
  (1, 'Alpha'),
  (2, 'Beta'),
  (3, 'Gamma'),
  (4, 'Delta');

Validated query

SQL

SELECT
  id,
  name,
  CASE
    WHEN MOD(id, 2) = 0 THEN 'even'
    ELSE 'odd'
  END AS parity
FROM
  items
ORDER BY
  id;

Expected result

id	name	parity
1	Alpha	odd
2	Beta	even
3	Gamma	odd
4	Delta	even

SQL Server SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE items (id INT, name VARCHAR(50));

INSERT INTO
  items
VALUES
  (1, 'Alpha'),
  (2, 'Beta'),
  (3, 'Gamma'),
  (4, 'Delta');

Validated query

SQL

SELECT id, name, CASE WHEN id % 2 = 0 THEN 'even' ELSE 'odd' END AS parity FROM items ORDER BY id;

Expected result

id	name	parity
1	Alpha	odd
2	Beta	even
3	Gamma	odd
4	Delta	even

MySQL/MariaDB/PostgreSQL use MOD(id, 2). SQL Server and SQLite use the % operator.

Useful when

Where this command helps.

finding rows where a value deviates more than a threshold from a target
filtering even or odd rows by checking the remainder after division by 2

Explanation

What the command is doing.

ABS(n) returns the non-negative magnitude of n — useful for comparing distances or deviations without caring about direction. MOD(n, d) returns the remainder after dividing n by d; a result of 0 means n is evenly divisible by d. MySQL, MariaDB, and PostgreSQL accept MOD(n, d) as a function; SQL Server and SQLite use the % operator. PostgreSQL also supports % as an operator alias. All engines support ABS.

Compute Absolute Value and Remainder

Find the absolute deviation of each temperature reading from a target

Label rows as even or odd using MOD

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.