MySQL Modulo

MySQL Modulo Operator

The MySQL % operator is used to find the remainder of the division between two numbers. This operator is essential for performing arithmetic calculations where you need the remainder.

Syntax

SELECT column1 % column2 AS result
FROM table_name;

The % operator has the following components:

  • column1: The numerator column or value.
  • column2: The denominator column or value.
  • result: An alias for the resulting value.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL Modulo Operator

Let's look at some examples of the MySQL % operator:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    total_hours INT NOT NULL,
    days_worked INT NOT NULL
);

This query creates a table named employees with columns for id, total_hours, and days_worked.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (total_hours, days_worked)
VALUES (160, 7),
       (150, 6),
       (130, 5);

This query inserts three rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Finding the Remainder of Two Columns

Find the remainder when dividing one column by another and display the result:

SELECT total_hours % days_worked AS hours_remainder
FROM employees;

This query finds the remainder when dividing the total_hours column by the days_worked column and displays the result as hours_remainder.

MySQL MODULO TWO COLUMNS

Step 5: Finding the Remainder with a Constant

Find the remainder when dividing a column by a constant value:

SELECT total_hours % 5 AS remainder_five
FROM employees;

This query finds the remainder when dividing the total_hours column by a constant value of 5, displaying the result as remainder_five.

MySQL MODULO COLUMN AND CONSTANT

Step 6: Finding the Remainder with Multiple Columns

Find the remainder with multiple columns and display the result:

SELECT (total_hours % days_worked) % 3 AS adjusted_remainder
FROM employees;

This query finds the remainder when dividing the total_hours column by the days_worked column and then finds the remainder when that result is divided by 3, displaying the result as adjusted_remainder.

MySQL MODULO MULTIPLE COLUMNS

Conclusion

The MySQL % operator is a powerful tool for performing arithmetic calculations in SQL queries. Understanding how to use the % operator is essential for effective data manipulation and analysis in MySQL.