**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:

1 | MOD(a,b); |

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:

1 | 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 |
---|---|---|---|

`11` | `4` | `3` | `3` |

`11` | `-4` | -1 | `3` |

`-11` | `4` | `2` | `-3` |

`-11` | `-4` | `-3` | `-3` |

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.

1 2 | SELECT 33/5 as integer, MOD(33,5) as remainder; |

1 2 3 4 | integer | remainder ---------+----------- 6 | 3 (1 row) |

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

1 | SELECT 13 % 3 AS remainder; |

1 2 3 4 | remainder ----------- 1 (1 row) |

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.

1 2 3 4 5 6 7 8 9 | SELECT CASE MOD(employee_id, 2) WHEN 0 THEN 'Even' ELSE 'Odd' END AS team, COUNT(employee_id) FROM employees 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.