MySQL MOD Function

Summary: in this tutorial, you will learn how to use the MySQL MOD() function to return the remainder when dividing one number by another.

Introduction to MySQL MOD() function

The MOD() function returns the remainder of one number divided by another number.

Here’s the basic syntax of the MOD() function:

MOD(dividend,divisor)Code language: SQL (Structured Query Language) (sql)

The MOD() function accepts two arguments:

• dividend is a literal number or a numeric expression that you want to divide.
• divisor is a literal number or a numeric expression by which you want to divide the dividend.

The MOD() function returns the remainder of the dividend divided by the divisor.

If the dividend or divisor is NULL, the MOD() function returns NULL. Also, if the divisor is zero, the MOD(dividend, 0) returns NULL.

To make the query more concise, you can use the modulo operator (%) instead of the MOD() function as they are synonyms:

dividend % divisor

MySQL MOD() function examples

Let’s take some examples of using the MOD() function.

1) Simple MOD() function examples

The following statement uses the MOD() function to get the remainder of the division of 11 and 3. It returns 2 as the integer portion of the result:

SELECT MOD(11, 3);Code language: SQL (Structured Query Language) (sql)

Output:

The following shows the equivalent query but uses the % operator:

SELECT 11 % 3Code language: SQL (Structured Query Language) (sql)

Output:

The MOD() function also works on values with a fractional part and returns the remainder after division. For example:

SELECT MOD(10.5, 3);Code language: SQL (Structured Query Language) (sql)

The following picture illustrates the output:

2) Using the MOD() function with data in a table example

We will use the orderDetails from the sample database for the demonstration:

The following statement uses the MOD() function to determine whether the quantity of products that the customer ordered is odd or even:

SELECT 
    orderNumber,
    SUM(quantityOrdered) Qty,
    IF(MOD(SUM(quantityOrdered),2),
        'Odd',
        'Even') oddOrEven
FROM
    orderdetails
GROUP BY 
    orderNumber
ORDER BY
    orderNumber;    
Code language: SQL (Structured Query Language) (sql)

In this example:

• First, use the SUM() function to get the total quantity of products by sales order.
• Second, use the MOD() function to obtain the remainder of the total quantity divided by two. This results in zero or one, depending on where the total quantity is even or odd.
• Third, use the IF() function to display the Odd and Even string based on the result of the MOD() function.

Output:

Summary

• Use the MySQL MOD() function to get the remainder when dividing one number by another.
• MySQL uses the % operator as the synonym for the MOD() function.