This SQL tutorial focuses on SQL Arithmetic Operators in MySQL, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.
This tutorial is a part of several posts explaining how to write basic queries in MySQL. To read additional posts regarding this subject, please use the following links:
- Writing Basic SQL SELECT statements
- SQL Arithmetic Operators
- SQL Column Aliases
- SQL String Concatenation
- SQL DISTINCT keyword
MySQL Arithmetic Operators
You may not always want to retrieve the data as is. In some cases, you may want to display your data with certain calculation, or look at a what-if scenarios (for example, you may want to know how the prices would look like after adding VAT).
The MySQL query in this example calculates the book’s price after an addition of 20%.
SELECT bookName , Publisher, bookPrice, bookPrice * 1.2 FROM books
It is advisable to specify the original column without the change (bookPrice) in addition to the column with the calculation (bookPrice *1.2), in order to make the difference – in this example, the increase – visible.
It is possible to perform any mathematical calculation:
SELECT bookName , Publisher , bookPrice,
bookPrice * 0.14 / 2.74 + 52.36
FROM books
In order to define operator precedence, simply use round brackets:
SELECT bookName , Publisher , bookPrice,
bookPrice * 0.14 / (2.74 + 52.36)
FROM books
In MySQL, You can also use arithmetic operators on multiple columns:
SELECT bookName , bookPrice , Discount
bookPrice - Discount
FROM books
Note : MySQL syntax Ignores blank spaces before and after the arithmetic operator.
Arithmetic Operations on NULL Values
In MySQL A NULL value is a value that indicates an empty field in a table. This value does not equal zero (0), nor does it equal space (‘ ‘); When a certain calculation is carried out on a column that contains NULL values, any calculation performed on the NULL value returns NULL.
SELECT bookName, bookPrice, discount,
discount + 1
FROM books
SELECT bookName, bookPrice, discount ,
discount * bookPrice
FROM books
The discount column is a column that contains NULL values. Each calculation on a field in this column that contains NULL value returns NULL.
General Guidelines
- In MySQL calculations on columns (of any data type) do not modify the contents (actual data) of the column in the table, but only display the column’s contents for that specific query, in another way.
- In MySQL Calculations on columns change the column’s header’s in the output (this issue will be addressed in the next post – Column Aliases)
SELECT bookName, bookPrice, bookPrice + 500 FROM books
Result :
bookName bookPrice bookPrice + 500 ------------ ---------- ----------------- Tea 30 530
