SQL MOD Function

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.

Here’s 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 function 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 |         3Code 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)

Output:

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

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

Let’s assume you want to divide the employees into Odd and Even teams.

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)

Try it

Output:

 team | count
------+-------
 Even |    21
 Odd  |    19

Summary #

• Use the SQL MOD function to get the modulo of a number divided by another.