SQL MOD: Returns the Modulus of a Number Divided by Another

Summary: in this tutorial, you will learn how to use the SQL MOD function that returns the remainder of one number divided by another.

Introduction to the SQL MOD function

The MOD function returns the remainder (modulus) of a number divided by another. The following illustrates the syntax of the MOD function:

MOD(a,b);Code language: SQL (Structured Query Language) (sql)

The MOD function accepts two arguments.

• a is a number or numeric expression to divide.
• b is the divisor which is also a number or expression by which to divide the dividend.

If b is 0, most database systems will issue a division by zero error except for Oracle database. Oracle database will return the dividend (a) instead.

Most database systems use the following formula to calculate the modulus except for Oracle and MySQL:

a - b*(FLOOR(a/b));Code language: SQL (Structured Query Language) (sql)

The following table illustrates the result when using the MOD function for calculating the modulus.

Besides the MOD function, some database systems provide a built-in modulo operator % such as Microsoft SQL, MySQL, and PostgresQL that is equivalent to the MOD function.

SQL MOD examples

The following statement divides the number 33 by 5 that results in 6 as the integer portion of the result and 4 as the remainder.

SELECT 33/5 as integer, 
       MOD(33,5) as remainder;Code language: SQL (Structured Query Language) (sql)

 integer | remainder
---------+-----------
       6 |         3
(1 row)Code language: SQL (Structured Query Language) (sql)

You can use the modulo operator (%) to get the modulus of 13 divided by 3 as follows:

SELECT 13 % 3 AS remainder;
Code language: SQL (Structured Query Language) (sql)

 remainder
-----------
         1
(1 row)Code language: SQL (Structured Query Language) (sql)

Let’s take a look at the employees table in the sample database.

Assuming you need to divide the employee into two teams: Odd and Even. The Odd team includes the employees whose employee ids are odd, and the Even team includes the employees whose employee ids are even.

The following statement uses the MOD function in a CASE expression to assign employees to the Odd or Even team. The GROUP BY clause groups employees by team and the COUNT function returns the number of employees in each team.

SELECT 
    CASE MOD(employee_id, 2)
        WHEN 0 THEN 'Even'
        ELSE 'Odd'
    END AS team,
    COUNT(employee_id)
FROM
    employees
GROUP BY team;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL MOD function to get the modulo of a number divided by another.