MODULUS Operator (%) - SQL

Overview

The Modulus operator (%) in SQL returns the remainder of the division of one number by another. It can be used in SQL queries to perform arithmetic modulus operations on numeric columns or values.

Syntax:

SELECT number1 % number2

number1 % number2 calculates the remainder when number1 is divided by number2.

Sample Data:

    
            first_name
            vacation_days
        
            Frank
            5
        
            Jane
            2
        
            Ashley
            3
        
            Glenn
            -3
        
            Kelly
            2
        
            Richard
            -7
        
            George
            2
        
            Kyle
            1
        
            James
            -2
        
            Gustavo
            -10

first_name	vacation_days
Frank	5
Jane	2
Ashley	3
Glenn	-3
Kelly	2
Richard	-7
George	2
Kyle	1
James	-2
Gustavo	-10

Example: Hard-coded values

In this example, the Modulus operator is used to return the remainder of 5 divided by 2.

Example:

SELECT 5 % 2

Example: Query without the WHERE Statement

In this example, we are returning the vacation_days column and a calculated column which returns the remainder from each value in the vacation_days column divided by 3. The final output is the original vacation_days column alongside the remainders values column.

Example:

SELECT vacation_days, vacation_days % 3
FROM company.employees

Example: Query with the WHERE Statement

In this example, we are filtering for only rows in department_id 1. We are returning the vacation_days column and a calculated column which returns the remainder from each value in the vacation_days column divided by 3. The final output is the original vacation_days column alongside the remainders values column.

Example:

SELECT vacation_days, vacation_days % 3
FROM company.employees
WHERE department_id = 1