MySQL Integer Division

MySQL Integer Division Operator

The MySQL DIV operator is used to perform division between two integers, returning an integer result. This operator is essential for performing arithmetic calculations where fractional values are not needed.

Syntax

SELECT column1 DIV column2 AS result
FROM table_name;

The DIV operator has the following components:

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

Example MySQL Integer Division Operator

Let's look at some examples of the MySQL DIV 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, 20),
       (120, 15),
       (180, 22);

This query inserts three rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Performing Integer Division

Perform integer division between two columns and display the result:

SELECT total_hours DIV days_worked AS average_hours_per_day
FROM employees;

This query performs integer division between the total_hours and days_worked columns and displays the result as average_hours_per_day.

MySQL INTEGER DIVISION

Step 5: Integer Division with a Constant

Perform integer division between a column and a constant value:

SELECT total_hours DIV 8 AS full_days_worked
FROM employees;

This query performs integer division between the total_hours column and a constant value of 8, displaying the result as full_days_worked.

MySQL INTEGER DIVISION WITH CONSTANT

Step 6: Integer Division with Multiple Columns

Perform integer division with multiple columns and display the result:

SELECT total_hours DIV days_worked DIV 2 AS adjusted_average_hours
FROM employees;

This query performs integer division between the total_hours and days_worked columns and then divides the result by 2, displaying the result as adjusted_average_hours.

MySQL INTEGER DIVISION MULTIPLE COLUMNS

Conclusion

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