MySQL MOD(): Remainder After Division

MOD() returns the remainder after division. MySQL also supports the % and MOD operators.

SELECT MOD(100, 20) AS r1,
       MOD(52, 25) AS r2,
       24 % 7 AS r3,
       29 MOD 9 AS r4;

Find even and odd IDs

Using the student table, an even ID has a remainder of zero when divided by 2.

SELECT *
FROM student
WHERE MOD(id, 2) = 0;

Odd IDs:

SELECT *
FROM student
WHERE MOD(id, 2) <> 0;

Limit the even-ID result to five rows:

SELECT *
FROM student
WHERE MOD(id, 2) = 0
ORDER BY id
LIMIT 5;

Group rows by remainder

SELECT MOD(id, 2) AS remainder, COUNT(*) AS student_count
FROM student
GROUP BY MOD(id, 2)
ORDER BY remainder;

The same grouping can be combined with AVG(), MAX() or MIN():

SELECT MOD(id, 2) AS remainder,
       AVG(mark) AS average_mark,
       MAX(mark) AS maximum_mark,
       MIN(mark) AS minimum_mark
FROM student
GROUP BY MOD(id, 2);

Use MOD() in UPDATE conditions

UPDATE student
SET address = CONCAT(name, '_address')
WHERE MOD(id, 2) = 0;

NULL and division by zero

If either argument to MOD() is NULL, the result is NULL. Division by zero also yields NULL with an appropriate warning depending on SQL mode.

SELECT MOD(NULL, 10) AS null_dividend,
       MOD(10, NULL) AS null_divisor,
       MOD(10, 0) AS zero_divisor;

SQL dump of student3 table

Related math tutorials

All Math Functions · WHERE · GROUP BY · COUNT() · NULL values

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: