MOD : the remainder of a division between two numbers

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
idnameclassmarkgender
2Max RuinThree85male
4Krish StarFour60female
6Alex JohnFour55male
8AsruidFive85male
10Big JohnFour55female
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
118
017
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
196
094
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
idnameclassmark
32Binn RottSeven90
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
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer