MOD() Function in MySQL

The MOD() function in MySQL is used to find the remainder of one number divided by another. The MOD() function returns the remainder of dividend divided by divisor. if the divisor is zero, it returns NULL.

Syntax :

MOD(N, M)
or
N % M
or
N MOD M

Parameter :
MOD() function accepts two parameter as mentioned above and described below.

  • N –The dividend i.e.  a  number or a numeric expression that will be divided by M.
  • M –The divisor i.e. a number or a numeric expression by which to divide the dividend.

Returns :
It returns the remainder of dividend divided by divisor.

Example-1 :
Finding the remainder of 36 when it is divided by 6 using MOD Function.



SELECT MOD( 36, 6) AS Remainder;

Output :

Remainder 
0

Example-2 :
Finding the remainder of 27 when it is divided by 4 using the modulus operator(%).

SELECT 27 % 4 AS Remainder;

Output :

Remainder 
3

Example-3 :
Finding the remainder of a floating number using MOD Function.

SELECT 10.15 MOD 3  AS Remainder;

Output :

Remainder 
1.15

Example-4 :
Finding the remainder of a  number using  MOD Function when the divisor is 0.

SELECT MOD( 6, 0) AS Remainder;

Output :

Remainder
NULL

Example-5 :
MOD Function can also be used to find the Remainder values for the column data. In this example, we are going to find whether a student has appeared a total odd number of exams or even with the help of the MOD function. To demonstrate create a table namedStudent.

CREATE TABLE Student
(
    Student_id INT AUTO_INCREMENT,  
    Student_name VARCHAR(100) NOT NULL,
    Student_Class VARCHAR(20) NOT NULL,
    TotalExamGiven INT   NOT NULL,
    PRIMARY KEY(Student_id )

);

Now inserting some data to the Student table :

INSERT INTO Student
(Student_name, Student_Class, TotalExamGiven )
VALUES
    ('Sayan', 'IX', 8 ),
    ('Nitin', 'X',  5 ),
    ('Aniket', 'XI', 6 ),
    ('Abdur', 'X',  7 ),
    ('Riya', 'IX', 4 ),
    ('Jony', 'X', 10 ),
    ('Deepak', 'X',  7 ),
    ('Ankana', 'XII', 5 ),
    ('Shreya', 'X',  8 ) ;

So, the Student Table is as follows.

mysql> SELECT * FROM Student;
+------------+--------------+---------------+----------------+
| Student_id | Student_name | Student_Class | TotalExamGiven |
+------------+--------------+---------------+----------------+
|          1 | Sayan        | IX            |              8 |
|          2 | Nitin        | X             |              5 |
|          3 | Aniket       | XI            |              6 |
|          4 | Abdur        | X             |              7 |
|          5 | Riya         | IX            |              4 |
|          6 | Jony         | X             |             10 |
|          7 | Deepak       | X             |              7 |
|          8 | Ankana       | XII           |              5 |
|          9 | Shreya       | X             |              8 |
+------------+--------------+---------------+----------------+
9 rows in set (0.00 sec)

Now, we are going to find whether a student has appeared total odd number of exam or even.

SELECT 
    Student_name,
    Student_Class,
    TotalExamGiven,
    IF(MOD(TotalExamGiven, 2),
    'Odd','Even') 
    OddOrEven FROM Student ;    

Output :

+--------------+---------------+----------------+-----------+
| Student_name | Student_Class | TotalExamGiven | OddOrEven |
+--------------+---------------+----------------+-----------+
| Sayan        | IX            |              8 | Even      |
| Nitin        | X             |              5 | Odd       |
| Aniket       | XI            |              6 | Even      |
| Abdur        | X             |              7 | Odd       |
| Riya         | IX            |              4 | Even      |
| Jony         | X             |             10 | Even      |
| Deepak       | X             |              7 | Odd       |
| Ankana       | XII           |              5 | Odd       |
| Shreya       | X             |              8 | Even      |
+--------------+---------------+----------------+-----------+
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.