MySQL MOD() function

In this context, we will learn how we can use the MySQL MOD() function with proper syntax and good examples.

Introduction of MySQL MOD() function

Basically, in MySQL, the MOD() function is used to retrieve the Remainder of one number divided by another. The remainder of the dividend divided by the divisor is returned by the MOD() function in MySQL. It will return NULL when the divisor is zero.

Syntax of the MySQL MOD() function

The syntax of the MySQL MOD() function is given as follows:

MOD(P, Q)
or
P % Q
or
P MOD Q

Parameters or arguments used in MySQL MOD() function:

There are two parameters accepted by the MOD() function in MySQL, which are given as follows:

P: It is the dividend, which is a numeric expression or a number that will be divided by Q.

Q: It is the divisor, which is a numeric expression or a number by which to divide the dividend.

Returns:

It will return the Remainder of the dividend divided by the divisor.

Application used for MOD() function:

The MOD() function can be used in the given below MySQL versions.:

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Examples of MySQL MOD() function:

Now we will look into some MySQL MOD() function examples and will explore how we can use the MOD function in MySQL.

Example-1:

Now we will derive the Remainder of 25 when it is divided by 5 using MOD Function in MySQL.

SELECT MOD( 25, 5) AS Remainder;

Output:

Remainder
0
1 row in set (0.00 sec)

Example-2:

Now we will derive the Remainder of 23 when it is divided by 5 using the modulus operator(%) in MySQL.

SELECT 23 % 5 AS Remainder;

Output:

Remainder
3
1 row in set (0.00 sec)

Example-3:

In this example, we will derive the Remainder of a floating number using the MOD Function in MySQL.

SELECT 10.15 MOD 3 AS Remainder;

Output:

Remainder
1.15
1 row in set (0.00 sec)

Example-4:

In this example, we will derive the Remainder of a number using the MOD Function when the divisor is 0 in MySQL.

SELECT MOD( 6, 0) AS Remainder;

Output:

Remainder
NULL
1 row in set (0.00 sec)

Example-5:

When we require to find the Remainder value of column data, then we can take the help of the MOD function. In this given an example, we will find whether a pupil has appeared in a total odd number of exams or even with the help of the MOD function. For demonstration, we have created a table named pupil.

CREATE TABLE Pupil
(
Pupil_id INT AUTO_INCREMENT,
Pupil_name VARCHAR(100) NOT NULL,
Pupil_Class VARCHAR(20) NOT NULL,
TotalExamGiven INT NOT NULL,
PRIMARY KEY(Pupil_id )


);

Now, insert some data into the Pupil table:

INSERT INTO Pupil
(Pupil_name, Pupil_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 Pupil Table is as follows.

mysql> SELECT * FROM Pupil;

Pupil_id	Pupil_name	Pupil_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 pupil has appeared in a total odd number of exams or even.

SELECT
Pupil_name,
Pupil_Class,
TotalExamGiven,


IF(MOD(TotalExamGiven, 2),
'Odd,' 'Even')
OddOrEven FROM Pupil ;

Output:

Pupil_name	Pupil_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

9 rows in set (0.00 sec)

Summary:

In the above context, we have learned how we can use the MOD() function in MySQL used to find the Remainder of one number divided by another.