Mod math mysql functions to get reminder of a division

Mod is a math function of MySQL which returns the reminder after a division. We will try to use this function in our query to get different results. Here are some examples.

SELECT MOD(100,20)   // output is 0
SELECT MOD(52,25)     //Output is 2
SELECT  24%7         //Output is 3

We can use mod function in our student table
Getting all the records for which id number is even by using WHERE condition check

SELECT * FROM `student` WHERE mod(id,2)=0

Getting all records for which id number is odd

SELECT * FROM `student` WHERE id%2 !=0;

Another way we can also write

SELECT * FROM `student` WHERE id%2=0

Let us collect first five records by using LIMIT.

SELECT * FROM `student` WHERE id%2=0 LIMIT 0,5

id	name	class	mark	gender
2	Max Ruin	Three	85	male
4	Krish Star	Four	60	female
6	Alex John	Four	55	male
8	Asruid	Five	85	male
10	Big John	Four	55	female

By using aggregrate function COUNT() along with GROUP BY, we can get number of even or odd id numbers in our student table.

SELECT  MOD(id,2), COUNT(id) as no FROM `student` GROUP BY MOD(id,2)

MOD(id,2)	no
1	18
0	17

Average mark of all even and odd ids of students by using AVG()

SELECT  MOD(id,2), AVG(mark) as no FROM `student` GROUP BY MOD(id,2)

Maximum mark of all even and odd ids of students by using MAX()

SELECT  MOD(id,2), MAX(mark) as no FROM `student` GROUP BY MOD(id,2);

MOD(id,2)	no
1	96
0	94

Minimum mark of all even and odd ids of students by using MIN()

SELECT  MOD(id,2), MIN(mark) as no FROM `student` GROUP BY MOD(id,2);

Updating all the records with even number of student id by using concat function

UPDATE student set address = concat(name,'_address') where MOD(id,2)=0

How SQL MOD() handles NULL value?

Here's a breakdown of how MOD() handles NULL data in various scenarios:

`MOD(NULL, number)`: If the first argument (dividend) is `NULL`, the result is `NULL`, indicating that the outcome of the operation cannot be determined because one of the inputs is unknown.
`MOD(number, NULL)`: If the second argument (divisor) is `NULL`, the result is also `NULL`, since dividing by an unknown quantity does not produce a determinable result.
`MOD(NULL, NULL)`: If both arguments are `NULL`, the result is `NULL`, because the operation involves two unknown values, making the outcome indeterminate.

Our student3 table has null data.

SELECT * FROM `student3` WHERE MOD(mark,10)=0;

Output of this query is here

id	name	class	mark
32	Binn Rott	Seven	90

SQL dump of student3 table

Practical Examples

Financial Analysis: Calculating recurring payment schedules by dividing the day of the month by payment intervals.
Logistics and Operations: Organizing delivery schedules by segmenting orders into batches based on modulo operations.
Database Maintenance: Implementing rotation logs or archive systems where entries are cycled based on a modulo of dates or entry IDs.

SQL Math References SIGN function : Sign of the argument

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:

Subscribe to our YouTube Channel here

MOD : the remainder of a division between two numbers

How SQL MOD() handles NULL value?

Practical Examples

Subhendu Mohapatra