This tutorial is a part of several posts explaining how to write basic queries in SQL Server. 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
SQL Server 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).
Arithmetic Operations on Numeric Values
This SQL Server example calculates the employee’s salary after an addition of 20%.
SELECT last_name , first_name, salary, salary * 1.2 FROM employees
It is advisable to specify the original column without the change (salary) in addition to the column with the calculation (salary *1.2), in order to make the difference – in this example, the increase – visible.
It is possible to perform any mathematical calculation:
SELECT last_name , first_name , salary, salary * 0.14 / 2.74 + 52.36 FROM employees
In order to define operator precedence, simply use round brackets:
SELECT last_name , first_name , salary, salary * 0.14 / (2.74 + 52.36) FROM employees
In SQL Server, You can also use arithmetic operators on multiple columns:
SELECT ProductName , ProductPrice , Discount ProductPrice - Discount FROM products
Note : SQL Server syntax Ignores blank spaces before and after the arithmetic operator.
Arithmetic Operations on String Values
In SQL Server It is not possible to perform any calculations on String values (such as Varchar or Char).
SELECT last_name , first_name + 300 FROM employees (error)
Arithmetic Operations on Date Values
In SQL Server The plus (+) and minus (-) operators can be used to perform arithmetic operations on Date values. These operators will allow you to add or subtract days.
SELECT last_name , first_name , hire_date, hire_date + 1 FROM employees
Result: Worker’s employment date (+) one day
SELECT last_name , first_name , hire_date, hire_date - 1 FROM employees
Result: Worker’s employment date (-) one day
In SQL Server It is not possible to carry out any calculation of (multiplication) or (division) on Date values.
SELECT last_name , first_name , hire_date, hire_date * 3 FROM employees (error) SELECT last_name , first_name , hire_date, hire_date / 7 FROM employees (error)
Arithmetic Operations on NULL Values
In SQL Server 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 last_name, salary, commission_pct, commission_pct + 1 FROM employees SELECT last_name, salary, commission_pct , salary * commission_pct FROM employees
The commission_pct column is a column that contains NULL values. Each calculation on a field in this column that contains NULL value returns NULL.
- In SQL Server 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 SQL Server Calculations on columns change the column’s header’s in the output (this issue will be addressed in the next post – Column Aliases)
SELECT last_name, salary, salary + 500 FROM employees
last_name salary (No Column Name) ------------ ------ ---------------- Doe 5700 6200