What does the MOD function do in SQL?

The MOD function in SQL is used to calculate the remainder of a division between two numbers. It’s similar to the modulo operator (%) in many programming languages.

Syntax: #

MOD(number1, number2)

number1 → the dividend

number2 → the divisor

Returns the remainder after dividing number1 by number2.

Example:

SELECT MOD(17, 5) AS remainder;

Result:

Here, 17 divided by 5 is 3 with a remainder of 2.

Real-world scenario: #

I used MOD in a reporting scenario to identify alternating patterns, such as splitting customers into two groups for A/B testing:

SELECT customer_id, 
       CASE WHEN MOD(customer_id, 2) = 0 THEN 'Group A' ELSE 'Group B' END AS test_group
FROM customers;

This evenly distributes customers into two test groups based on their IDs.

Challenges: #

• MOD can behave differently with negative numbers depending on the database system (e.g., some return negative remainders, others return positive). I always verified results before using it in business logic.

Limitations: #

• Only works with numeric values; cannot be applied directly to strings or dates.

Alternatives/Enhancements: #

• Some databases allow using the % operator as a shorthand:

SELECT 17 % 5 AS remainder;

• For advanced use, combining MOD with CASE statements allows grouping, batching, or cyclical logic in queries.

In summary, the MOD function is a simple but powerful tool to find remainders, implement cyclical patterns, or divide records into groups in SQL queries.