MySQL MOD Function
Summary: in this tutorial, you will learn how to use the MySQL MOD() function to return the remainder of one number divided by another.
MySQL MOD() function syntax
The MOD() function returns the remainder of one number divided by another. The following shows the syntax of the MOD() function:
MOD(dividend,divisor)
Code language: SQL (Structured Query Language) (sql)
The MOD() function accepts two arguments:
dividendis a literal number or a numeric expression to divide.divisoris a literal number or a numeric expression by which to divide the dividend.
The MOD() function returns the remainder of dividend divided by divisor. if the divisor is zero, the MOD(dividend, 0) returns NULL.
MySQL MOD() function examples
Simple MOD() function examples
The following statement divides the number 11 by 3. It returns 2 as the integer portion of the result:
SELECT MOD(11, 3);
Code language: SQL (Structured Query Language) (sql)
Here is the output:
MySQL allows you to use the modulus operator (%) which is the synonym for MOD() function as follows:
SELECT 11 % 3
Code language: SQL (Structured Query Language) (sql)
The output is:
The MOD() function also works on values with a fractional part and returns the remainder after division. See the following example:
SELECT MOD(10.5, 3);
Code language: SQL (Structured Query Language) (sql)
The following picture illustrates the output:
Using MOD() function on table columns
We will use the orderDetails from the sample database for the demonstration:
The following statement finds whether the quantity of products which 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, we used the
SUM()function to get the total quantity of products by sales order. - Then, we used the
MOD()function to find 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. - Finally, we used the
IF()function to display theOddandEvenstring based on the result of theMOD()function.
Here is the output:
In this tutorial, you have learned how to use the MySQL MOD() function to return the remainder of a number divided by another.