Read Prerequisites for this tutorial and practices if you haven't done so.

Consider the following facts when using arithmetic operations in a SELECT statement:

1. There are seven arithmetic operators: Addition, Subtraction, Multiplication, Division, Modulo, DIV, Unary minus.

Operator	Description
+	Addition operator
-	Minus operator
*	Multiplication operator
/	Division operator
%	Modulo operator
DIV (v4.1.0)	Integer division
-	Unary minus. It changes the sign of the argument.

2. Similar to basic arithmetic calculations, arithmetic operators in SQL also have Operator Precedence.

   If the arithmetic expression contains more than one operator, multiplication operator and division operator are evaluated first, and then addition and minus operator are evaluated. When two operators have the same priority, the expression is evaluated from left to right.

   Here is the list of arithmetic operator precedences in MySQL, from the highest precedence to the lowest. Operators that are shown on the same row have the same precedence.

   Order Evaluated	Operators
   1	- (unary minus)
   2	*	/	%	DIV
   3	+	-

3. Parentheses can be used to force an operation to take priority over any other operators. Parentheses are also used to improve code readability.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

Practice #1: Arithmetic operation in SQL SELECT statement.

/*
Query 1: This query calculates the total price before 
discount and after discount for each order item.
*/
SELECT OrderID, 
       ProductID, 
       UnitPrice*Quantity AS "Regular Price", 
       UnitPrice*Quantity-UnitPrice*Quantity*Discount AS "Price After Discount"
FROM order_details;
 
/*
Query 2: This query returns the same result as Query 1.
The only difference is in the formula used to 
calculate "Price After Discount". It shows the effect of
using parentheses to force an operation to take priority.
*/
SELECT OrderID, 
       ProductID, 
       UnitPrice*Quantity AS "Regular Price",
       (1-Discount)*UnitPrice*Quantity AS "Price After Discount"
FROM order_details;

Query result set - 2155 rows returned:

Practice #2: Modulo arithmetic operation in SQL SELECT statement.

/*
This query calculates the modulo of UnitsInStock by 3.
Modulo is the remainder of division of one number by another.
*/
SELECT UnitsInStock, UnitsInStock%3 AS "Modulo by 3"
FROM products;

Query result set - 77 rows returned:

Practice #3: Integer division arithmetic operation in SQL SELECT statement.

/*
Integer division returns the greatest integer 
value less than a quotient that is obtained 
by dividing one integer by another integer.
 
In this query, the division of 11 by 4 equals 2.75 and 
the integer division returns 2 which is the 
greatest integer less than 2.75
*/
SELECT 11 DIV 4;

Query result set:

Practice #4: Integer division arithmetic operation in SQL SELECT statement - round up.

/*
If the first value in integer division expression is 
not an integer, it will be rounded up to the next 
greater integer before integer division calculation 
is conducted.
 
In this query, 11.5 is rounded up to 12 first and then
integer division is calculated and returns 3.
*/
SELECT 11.5 DIV 4;

Query result set:

Practice #5: Use Unary minus to change the sign of an argument in SQL SELECT statement.

/*
Unary minus is an operator that changes the sign of the value.
 
In this query, minus 2 times minus 3 equals 6.
*/
SELECT -2*-3;

Query result set:

Other tutorials in this category

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Using Literal Character Strings

4. Use WHERE Clause to Conditionally Select Rows

5. Sorting Data

6. Using LIMIT Clause to Constrain the Number of Rows Retrieved

7. Using Comparison Operators, Part I

8. Using Comparison Operators, Part II

9. Using LIKE Comparison Operator

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page