Open In App

MOD() Function in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

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

Remainder
-1

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

SELECT 27 % 4 AS Remainder;

Output:

Remainder 
3

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

SELECT 10.15 MOD 3  AS Remainder;

Output :

Remainder 
1.15

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

SELECT MOD( 6, 0) AS Remainder;

Output :

Remainder
NULL

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

Last Updated : 24 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads