The MySQL Arithmetic Operators are used to perform Arithmetic operations on column data such as Addition, Subtraction, Multiplication, Division, and Modulus.
The following table shows you the list of available MySQL Arithmetic operators.
| MySQL Arithmetic Operators | Operation | Example |
|---|---|---|
| + | Addition Operator | SELECT 10 + 2 = 12 |
| – | Subtraction Operator | SELECT 10 – 2 = 8 |
| * | Multiplication Operator | SELECT 10 * 2 = 20 |
| / | Division Operator | SELECT 10 / 2 = 5 |
| DIV | Integer Division | SELECT 10 / 2 = 5 |
| % or MOD | Modulus Operator | SELECT 10 % 2 = 0 SELECT 10 % 3 = 1 |
In this article, we show you how to use these MySQL Arithmetic Operators with examples.
MySQL Arithmetic Operators Examples
The following are the list of example that helps you understand these operators to perform arithmetic operations.
MySQL Addition Operator
The MySQL Addition Operator is useful to add values. The following statements show you how to add values in MySQL. Within the third statement, we used the number in string format. However, MySQL converts them into an integer and returns the output of addition.
SELECT 10 + 2;
SELECT 10 + 20, 20 + 30;
SELECT '1990' + '200', '2005' + '2200';
OUTPUT
MySQL Subtraction Operator
The MySQL Subtract Operator is used to subtract one value from the other.
SELECT 10 - 2;
SELECT 20 - 5, 30 - 46;
SELECT '2150' - '1550', 1200 - '200';
OUTPUT
MySQL Multiplication Operator
The MySQL Multiplication Operator is used to Multiply one value with the other. Below MySQL query shows you the same
SELECT 10 * 2;
SELECT 5 * 3, 15 * 5;
SELECT 21 * '3', '250' * 5;
OUTPUT
MySQL Division Operator
The MySQL Division Operator Divides one value with the other. The examples below show you the same.
SELECT 10 / 2;
SELECT 2 / 3, 3 / 5;
SELECT 250 / '19', '1225' / 2, 12 / 0;
OUTPUT
MySQL DIV
In MySQL, you can use this DIV to perform division on integer values.
SELECT 20 DIV 3;
SELECT 5 DIV 2, -40 DIV 3, -100 DIV -7;
SELECT '220' DIV 3, 400 DIV '65';
OUTPUT
MySQL % Operator [Modulus]
The MySQL Modulus Operator or MOD function returns the remainder of the division.
SELECT 10 % 3;
SELECT 257 MOD 9, MOD(222, 19);
SELECT '2345' MOD 23;
OUTPUT
MySQL Arithmetic Operator Example 2
In this example, we are going to use these arithmetic operators on the column data. Fo this arithmetic operations demonstration, we are using the Sales Amount, Tax Amount, and Standard Cost columns.
SELECT EnglishProductName,
Color,
StandardCost,
SalesAmount,
TaxAmt,
SalesAmount + TaxAmt AS 'Total Amount',
SalesAmount - StandardCost AS 'Profit',
SalesAmount * 2 AS 'Multiplication Ex',
SalesAmount / TaxAmt,
SalesAmount % TaxAmt
FROM `MySQL Tutorial`.`product sales`;
OUTPUT
