Arithmetic Operators in SQL Server
Arithmetic operators are used to performing mathematical calculations such as Addition, Subtraction, Multiplication, and Division on the given operand values. That means these operators are used to perform mathematical operations on two expressions of same or different data types of numeric data.
These operators are
- + (Addition Operator)
- – (Minus Operator)
- * (Multiplication Operator)
- / (Division Operator)
- % (Modulo Operator)
Arithmetic operators Example:
SELECT 100 + 200 O/P = 300 SELECT 45 - 74 O/P = -29 SELECT 25 * 4 O/P = 100 SELECT 36 / 6 O/P = 6 SELECT 17 % 4 O/P = 1
Similar to basic arithmetic calculations, arithmetic operators in SQL Server also have the 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.
Parentheses can be used to force an operation to take priority over any other operators. Parentheses are also used to improve code readability.
Use below script to create and populate Employee table with some test data.
--Create database EmployeeDB Create database EmployeeDB Go Use EmployeeDB Go --Create Employee table CREATE TABLE Employee ( ID INT PRIMARY KEY IDENTITY(1,1), Name VARCHAR(100), EmailID VARCHAR(100), Gender VARCHAR(100), Department VARCHAR(100), Salary INT, Age INT, CITY VARCHAR(100) ) GO --Insert some data into Employee table INSERT INTO Employee VALUES('Pranaya','Pranaya@g.com','Male', 'IT', 25000, 30,'Mumbai') INSERT INTO Employee VALUES('Tarun','Tarun@g.com','Male', 'Payroll', 30000, 27,'Odisha') INSERT INTO Employee VALUES('Priyanka','Priyanka@g.com','Female', 'IT', 27000, 25,'Bangalore') INSERT INTO Employee VALUES('Preety','Preety@g.com','Female', 'HR', 35000, 26,'Bangalore') INSERT INTO Employee VALUES('Ramesh','Ramesh@g.com','Male','IT', 26000, 27,'Mumbai') INSERT INTO Employee VALUES('Pramod','Pramod@g.com','Male','HR', 29000, 28,'Odisha') INSERT INTO Employee VALUES('Anurag','Anurag@g.com','Male', 'Payroll', 27000, 26,'Odisha') INSERT INTO Employee VALUES('Hina','Hina@g.com','Female','HR', 26000, 30,'Mumbai') INSERT INTO Employee VALUES('Sambit','Sambit@g.com','Male','Payroll', 30000, 25,'Odisha') INSERT INTO Employee VALUES('Manoj','Manoj@g.com','Male','HR', 30000, 28,'Odisha') INSERT INTO Employee VALUES('Sara',' Sara@g.com','Female', 'Payroll', 28000, 27,'Mumbai') INSERT INTO Employee VALUES('Lima','Lima@g.com','Female','HR', 30000, 30,'Bangalore') INSERT INTO Employee VALUES('Dipak','Dipak@g.com','Male','Payroll', 32000, 25,'Bangalore') GO
Fetch the Records:
Select * From Employee
We may not always want to retrieve the data as it is. In some scenarios, we may want to display our data with certain calculation.
The below SQL Server example calculates the employee’s salary after an addition of 10%.
SELECT ID, Name, Department, Salary, Salary * 1.1 AS CalulatedSalary FROM Employee
It is always advisable to specify the original column without any change (salary) in addition to the column with the calculation (salary *1.1) in order to make the difference.
It is also possible to perform any mathematical calculation as shown in the below example.
SELECT ID, Name, Department, Salary, Salary * 0.11 / 2.54 + 27.36 AS CalulatedSalary FROM Employee
If we want to provide the order precedence then simply use round brackets as shown in the below example
SELECT ID, Name, Department, Salary, Salary * 0.11 / (2.54 + 27.36) AS CalulatedSalary FROM Employee
Note: SQL Server syntax Ignores blank spaces before and after the arithmetic operator. Along with, in SQL Server we can also use arithmetic operators on multiple columns:
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 ID, Name + 100 FROM Employee
Error: Conversion failed when converting the varchar value ‘Some String Value’ to data type int.
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 GETDATE() + 1 Gives Current date + 1 day
SELECT GETDATE() – 1 Gives Current date – 1 day
In SQL Server It is not possible to use Multiplication or Division on Date values.
SELECT GETDATE() * 5
SELECT GETDATE() / 5
Error: Implicit conversion from data type DateTime to int is not allowed. Use the CONVERT function to run this query.
Note: In SQL Server a NULL value is a value that indicates an empty field in a table. This value does not equal to zero (0), nor does it equals to space (‘ ‘); When we performed a certain calculation on a column that contains NULL values, then it returns NULL.
In the next article, I will discuss Comparison operators in SQL Server.
In this article, I try to explain the Arithmetic Operators in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.