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 :

Example-2 : Finding the remainder of -10 when it is divided by 3 using MOD Function.

SELECT MOD(-10, 3) AS Remainder;

Output : The MOD() function can also be used with negative numbers. In this case, the result will be negative if the dividend is negative, and positive if the dividend is positive. 

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

SELECT 27 % 4 AS Remainder;

Output:

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

SELECT 10.15 MOD 3  AS Remainder;

Output :

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

SELECT MOD( 6, 0) AS Remainder;

Output :

Example-6 : 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 named Student.

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 is as follows:

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      |
+--------------+---------------+----------------+-----------+

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• mysql