How to Get a Remainder Using MOD() in PostgreSQL, MS SQL Server, and MySQL

Database:

Operators:

MOD, ABS, CASE WHEN, SIGN

Problem:

You want to find the (non-negative) remainder.

Example:

In the table numbers, you have two columns of integers: a and b.

a	b
9	3
5	3
2	3
0	3
-2	3
-5	3
-9	3
5	-3
-5	-3
5	0
0	0

You want to compute the remainders from dividing a by b. Each remainder should be a non-negative integer value smaller than b.

Solution 1 (not entirely correct):

SELECT
  a,
  b,
  MOD(a, b) AS remainder
FROM numbers;

The result is:

a	b	remainder
9	3	0
5	3	2
2	3	2
0	3	0
-2	3	-2
-5	3	-2
-9	3	0
5	-3	2
-5	-3	-2
5	0	error
0	0	error

Discussion:

This solution works correctly if a is non-negative. However, when it is negative, it doesn’t follow the mathematical definition of the remainder.

Conceptually, a remainder is what remains after an integer division of a by b. Mathematically, a remainder of two integers is a non-negative integer that is smaller than the divisor b. More precisely, it is a number r∈{0,1,...,b - 1} for which there exists some integer k such that a = k * b + r. E.g.:

5 = 1 * 3 + 2, so the remainder of 5 and 3 equals 2.

9 = 3 * 3 + 0, so the remainder of 9 and 3 equals 0.

5 = (-1) * (-3) + 2, so the remainder of 5 and -3 equals 2.

This is how MOD(a, b) works for the non-negative dividends in the column a. Obviously, an error is shown if the divisor b is 0, because you can't divide by 0.

Getting the correct remainder is problematic when the dividend a is a negative number. Unfortunately, MOD(a, b) can return a negative value when a is negative. E.g.:

MOD(-2, 5) returns -2 when it should return 3.

MOD(-5, -3) returns -2 when it should return 1.

Solution 2 (correct for all numbers):

SELECT
  a,
  b,
  CASE WHEN MOD(a, b) >= 0
    THEN MOD(a, b)
  ELSE
    MOD(a, b) + ABS(b)
  END AS remainder
FROM numbers;

The result is:

a	b	remainder
9	3	0
5	3	2
2	3	2
0	3	0
-2	3	1
-5	3	1
-9	3	0
5	-3	2
-5	-3	1
5	0	error
0	0	error

Discussion:

To compute the remainder of a division between any two integers (negative or non-negative), you can use the CASE WHEN construction. When MOD(a, b) is non-negative, the remainder is simply MOD(a, b). Otherwise, we have to correct the result returned by MOD(a, b).

How do you get the correct remainder when MOD() returns a negative value? You should add the absolute value of the divisor to MOD(a, b). That is, make it MOD(a, b) + ABS(b):

MOD(-2, 5) returns -2 when it should return 3. You can fix this by adding 5.

MOD(-5, -3) returns -2 when it should return 1. You can fix this by adding 3.

When MOD(a, b) returns a negative number, the CASE WHEN result should be MOD(a, b) + ABS(b). This is how we get Solution 2. If you need a refresher on how the ABS() function works, take a look at the cookbook How to compute an absolute value in SQL.

Of course, you still can't divide any number by 0. So, if b = 0, you'll get an error.

Solution 3 (correct for all numbers):

SELECT
  a,
  b,
  MOD(a, b) + ABS(b) * (1 - SIGN(MOD(a, b) + 0.5)) / 2 AS remainder
FROM numbers;

The result is:

a	b	remainder
9	3	0
5	3	2
2	3	2
0	3	0
-2	3	1
-5	3	1
-9	3	0
5	-3	2
-5	-3	1
5	0	error
0	0	error

Discussion:

There is another way to solve this problem. Instead of a CASE WHEN, use a more complex one-line mathematical formula:

MOD(a, b) + ABS(b) * (1 - SIGN(MOD(a, b) + 0.5)) / 2

In Solution 2, MOD(a, b) + ABS(b) was returned for cases when MOD(a, b) < 0. Note that MOD(a, b) + ABS(b) = MOD(a, b) + ABS(b) * 1 when MOD(a, b) < 0.

In contrast, you return MOD(a, b) when MOD(a, b) >= 0. Note that MOD(a, b) = MOD(a, b) + ABS(b) * 0 when MOD(a, b) >= 0.

So, we can multiply ABS(b) by an expression that equals 1 for a negative MOD(a, b) and 0 for a non-negative MOD(a, b). Since MOD(a, b) is always an integer, the expression MOD(a, b) + 0.5 is always positive for MOD(a, b) ≥ 0 and negative for MOD(a, b) < 0. You can use any positive number less than 1 instead of 0.5.

The sign function SIGN() returns 1 if its argument is strictly positive, -1 if it is strictly negative, and 0 if it equals 0. However, you need something that returns only 0 and 1, not 1 and -1. Here is how you fix this:

(1 - 1) / 2 = 0

(1 - (-1)) / 2 = 1

Then, the correct expression by which you multiply ABS(b) is:

(1 - SIGN(MOD(a, b) + 0.5)) / 2

So, the entire formula is:

MOD(a, b) + ABS(b) * (1 - SIGN(MOD(a, b) + 0.5)) / 2