MySQL Arithmetic Operators 7

MySQL Arithmetic Operators

by

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 OperationExample
+Addition OperatorSELECT 10 + 2 = 12
–Subtraction OperatorSELECT 10 – 2 = 8
*Multiplication OperatorSELECT 10 * 2 = 20
/Division OperatorSELECT 10 / 2 = 5
DIVInteger DivisionSELECT 10 / 2 = 5
% or MODModulus OperatorSELECT 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';
MySQL Arithmetic Operators 1

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';
MySQL Arithmetic Operators 2

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;
MySQL Arithmetic Operators 3

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;
MySQL Arithmetic Operators 4

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';
MySQL Arithmetic Operators 5

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;
MySQL Arithmetic Operators 6

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`;
MySQL Arithmetic Operators 7