Custom Search
 


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:

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

  2. 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.

  3. 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 EvaluatedOperators
    1- (unary minus)
    2*/%DIV
    3+-

  4. 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.

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.

/*
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.

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.

/*
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.

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.

/*
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.

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.

/*
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





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


Copyright © 2014 GeeksEngine.com. All Rights Reserved.

This website is hosted by LunarPages.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy