How to Perform Arithmetic Operations in SQL SELECT Statement for MySQL

Perform Arithmetic Operations

When you need to perform calculations in SQL statement, you use arithmetic expression. An arithmetic expression can contain column names, numeric numbers, and arithmetic operators.

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:

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.

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 + -
Parentheses can be used to force an operation to take priority over any other operators. Parentheses are also used to improve code readability.

Practice #1: Arithmetic operation in SQL SELECT statement.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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:
Arithmetic operations in SQL SELECT

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:
Modulo operation in SELECT query

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:
Integer division in MySQL

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:
Integer division round up in MySQL

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:
Unary operation in MySQL SELECT statement

Next tutorial: Use WHERE Clause to Conditionally Select Rows
Back to Section Index: Single Table SELECT Statement
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search