The MOD() function in SQLite is used to calculate the remainder when one number is divided by another. This operation is commonly known as the modulo operation.

Syntax

MOD(numerator, denominator)

Where:

• numerator is the number to be divided.
• denominator is the number by which the numerator will be divided.

The function returns the remainder of the division of the numerator by the denominator.

Example 1

Here’s a simple example to demonstrate:

SELECT 11 / 3,
       MOD(11, 3);

Result:

+--------+------------+
| 11 / 3 | MOD(11, 3) |
+--------+------------+
| 3      | 2.0        |
+--------+------------+

Here we can see that dividing 11 by 2 equals 3, with a remainder of 2.

In other words, 3 multiplied by 3 equals 9, which means we need another 2 in order to get to 11. The 2 part is the “remainder”, which the MOD() function returns for us.

Example 2

Let’s say we have a table transactions with a column amount, and we want to find the remainder when the amount is divided by 10.

-- Create the 'transactions' table
CREATE TABLE transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    amount INTEGER
);

-- Insert sample data into the 'transactions' table
INSERT INTO transactions (amount)
VALUES
    (23),
    (57),
    (88);

Now let’s run a query to calculate the remainder when amount is divided by 10:

SELECT 
       amount, 
       amount / 10 AS division_result,
       MOD(amount, 10) AS remainder
FROM transactions;

In this example, for each row in the transactions table, SQLite will calculate the remainder of the amount when divided by 10 and return it as a new column named remainder.

Result:

+--------+-----------------+-----------+
| amount | division_result | remainder |
+--------+-----------------+-----------+
| 23     | 2               | 3.0       |
| 57     | 5               | 7.0       |
| 88     | 8               | 8.0       |
+--------+-----------------+-----------+

Here, for the first row, 23 divided by 10 leaves a remainder of 3, for the second row, 57 divided by 10 leaves a remainder of 7, and for the third row, 88 divided by 10 leaves a remainder of 8.