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
MOD function returns the remainder (modulus) of a number divided by another. The following illustrates the syntax of 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));
The following table illustrates the result when using the
MOD function for calculating the modulus.
|a||b||Oracle, MySQL||PostgreSQL, SQL Server, IBM DB2|
| || |
MOD function, some database systems provide a built-in modulo operator % such as Microsoft
SQL, MySQL, and PostgresQL that is equivalent to the
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;
integer | remainder
6 | 3
You can use the modulo operator (%) to get the modulus of 13 divided by 3 as follows:
SELECT 13 % 3 AS remainder;
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
BY clause groups employees by team and the
COUNT function returns the number of employees in each team.
CASE MOD(employee_id, 2)
WHEN 0 THEN 'Even'
END AS team,
GROUP BY team;
In this tutorial, you have learned how to use the SQL
MOD function to get the modulo of a number divided by another.