MOD() Function in SQL

The MOD() function in SQL is used for finding the remainder when a number is divided by another. It is very useful for performing the calculations, applying conditional logic, and organizing data into groups. The MOD() function in SQL is very helpful for checking whether a number is even or odd, creating custom filters, and simplifying numeric operations. In this blog, you will understand the MOD() function in SQL, along with its syntax, examples, common mistakes, and best practices to follow in detail.

Table of Contents:

• What is MOD() Function in SQL
• Syntax of MOD() Function in SQL
• Examples of Using the MOD() Function in SQL
  1. Basic Modulo Operation in SQL
  2. Using MOD() with Table Data in SQL
  3. Using MOD() in SQL Conditional Logic
  4. Using MOD() with Negative Numbers in SQL
  5. Using MOD() for Data Grouping and Partitioning in SQL
• Difference Between MOD() Function and % Operator in SQL
• Common Mistakes to Avoid
• Best Practices for Using MOD() Function
• Conclusion

What is MOD() Function in SQL

The MOD() function is a numeric function of SQL used to calculate the remainder when a number is divided by another number. This function helps in performing tasks such as numerical calculations, applying conditions in queries, grouping, and categorizing data based on these numeric patterns.

Key Features of MOD() Function in SQL:

1. Easy Calculations: The MOD() Function in SQL is used for performing calculations without using complicated formulas.
2. Supports Conditional Logic: It supports conditional logic and can be used with the WHERE clause to filter data based on numeric conditions.
3. Works within Queries: The MOD() function can be used in SELECT statements and with the ORDER BY clause to perform numeric calculations.
4. Simple to Use: It requires only two numeric inputs, which are the dividend and the divisor, for obtaining the resultant remainder.
5. Multiple Uses: It is useful for checking if a number is even or odd and for creating custom filters in reports.

Master SQL – From Beginner to Pro

Interactive lessons, real-world projects, live Q&A, and a certificate to showcase your skills.

Explore Program

Syntax of MOD() Function in SQL

In SQL, the MOD() function provides the remainder of a division. The function takes two numeric values: the first is the dividend and the second is the divisor.

Syntax:

MOD(dividend, divisor);

Explanation:

• dividend: The number being divided.
• divisor: The number that the dividend is being divided by.
• The result will be the remainder after the division.

NOTE: Let’s create a sample table that will be used throughout this blog to understand the working of the SQL MOD() function.

-- Creating the courses table
CREATE TABLE courses (
    course_id INT,
    course_name VARCHAR(50),
    student_name VARCHAR(50),
    marks INT
);

-- Inserting sample data
INSERT INTO courses (course_id, course_name, student_name, marks) VALUES
(1, 'Data Science', 'Lithin', 85),
(2, 'Artificial Intelligence', 'Riya', 90),
(3, 'Cloud Computing', 'Karan', 76),
(4, 'Full Stack Development', 'Sneha', 88),
(5, 'Data Science', 'Akshay', 92),
(6, 'Cyber Security', 'Anita', 81),
(7, 'Artificial Intelligence', 'Arjun', 95),
(8, 'Full Stack Development', 'Meena', 87);

-- Display the data
SELECT * FROM courses;

Output:

Examples of Using the MOD() Function in SQL

Now, let’s explore some of the techniques where the SQL MOD() function can be used, with detailed examples.

1. Basic Modulo Operation in SQL

The MOD() function in SQL provides the remainder of one number divided by another. This function can be used to calculate problems that require just the remainder portion of the calculation. The MOD() function can be used for categorizing, grouping, and applying restrictions in a query.

Example:

SELECT
    student_name,
    course_name,
    marks,
    MOD(marks, 5) AS Remainder
FROM courses;

Output:

Explanation: Here, the MOD() function divides the marks by 5 and displays only the remainder after the division of marks.

2. Using MOD() with Table Data in SQL

The MOD() function in SQL can be used directly on a column within the table to filter or sort data. It can provide value in understanding patterns in data. Such as checking if a number is odd or even, or if other patterns would group rows based on the remainder.

Example:

SELECT
    student_name,
    course_name,
    marks
FROM courses
WHERE MOD(marks, 2) = 0;

Output:

Explanation: Here, the MOD() function is used for checking whether the marks are divisible by 2.

3. Using MOD() in SQL Conditional Logic

The MOD() function is really useful when it is used in conditions. It allows you to divide records into categories or groups based on the result of the division. A common application is determining whether the number is even or odd with CASE statements.

Example:

SELECT
    student_name,
    course_name,
    marks,
    CASE
        WHEN MOD(marks, 2) = 0 THEN 'Even Marks'
        ELSE 'Odd Marks'
    END AS mark_type
FROM courses;

Output:

Explanation: Here, the CASE statement uses the MOD() function for checking the remainder and deciding whether the marks are odd or even.

4. Using MOD() with Negative Numbers in SQL

The MOD() function also works when negative numbers are used. It will perform the same function by returning the remainder of the division, while keeping the sign of the divisor. This is very useful in queries where you are calculating something that requires maintaining the integrity of negative values, and makes the calculations easy.

Example:

-- Query to calculate remainder using MOD() with negative marks
SELECT 
    student_name,
    course_name,
    -marks AS marks,       -- Making marks negative
    MOD(-marks, 5) AS remainder
FROM courses;

Output:

Explanation: The MOD() function calculates the remainder for each value, including negative numbers, and the result keeps the same sign as the divisor.

Note: The MOD() function behaves differently with negative numbers in different SQL databases. In Oracle/MySQL, MOD(-10, 3) gives -1, keeping the sign of the dividend. In PostgreSQL, it gives 2, keeping the sign of the divisor. Always check how your database handles negatives before using MOD().

5. Using MOD() for Data Grouping and Partitioning in SQL

The MOD() function is also very useful for organizing the data into groups or partitions. This is useful when the requirement is to distribute the rows evenly, for example, when students are to be assigned to different batches and split them evenly.

Example:

SELECT
    student_name,
    course_name,
    marks,
    MOD(marks, 3) AS group_no
FROM courses;

Output:

Explanation: Here, the MOD(marks, 3) assigns the students into three groups: 0, 1, or 2, based on the remainder, which helps in ensuring that the divisor is not zero to prevent errors.

Difference Between MOD() Function and % Operator in SQL

Common Mistakes to Avoid

1. Using the MOD() Function in SQL Server: SQL Server does not support the MOD() function. Instead, you should use the % operator for performing the same operation.
2. Zero as Divisor: Applying MOD(number,0) will return an error, as division by zero is not allowed in SQL.
3. Handling Negative Numbers: Different databases return different results while using negative numbers, so it is very important to test them before using them.
4. Missing ORDER BY with MOD(): SQL query results do not have a fixed order unless you use ORDER BY. To sort rows based on MOD() results, always include ORDER BY MOD(…).
5. Incorrect Syntax: If the syntax is wrong, for example, writing MOD column, 3 instead of MOD(column, 3), the query will not work.

Best Practices for Using MOD() Function

1. Use for Data Grouping: The MOD() function can group data, making analysis easier and more efficient.
2. Check the Divisor Values: It is important to double-check whether the divisor is zero, as this may lead to an error. 
3. Combine with the WHERE clause: Using MOD() with the WHERE clause allows conditions to be applied to filter rows.
4. Be Aware of Negative Numbers: Test the results when the MOD() function is applied to a negative number, since the results may vary.
5. Use with Joins or Aggregates: MOD() can be combined with joins or aggregate functions to solve more complex queries.

Kickstart Your SQL Journey – 100% Free

Structured lessons, real query practice, and solid foundations at zero cost.

Explore Program

Conclusion

The MOD() function in SQL is a very handy tool that is used for working with remainders and creating flexible queries. It also makes calculations easier and helps in checking whether numbers are even or odd, and is also useful for filtering and grouping data. Whether you are creating conditions, organizing the records, or analyzing the patterns of numeric data, MOD() is used to simplify the process. However, it is important to be careful with zero divisors, database-specific differences, and negative numbers. By using the best practices, you can use MOD() effectively to write cleaner queries, which will make your SQL operations efficient and organized.

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions, prepared by industry experts.

MOD() Function in SQL – FAQs