• Article

Arithmetic Operators (Database Engine)

Arithmetic operators can be used for any arithmetic computations, including the following:

  • Addition

  • Subtraction

  • Multiplication

  • Division

  • Modulo (the remainder from a division operation)

Following is information about arithmetic operators:

  • When there is more than one arithmetic operator in an expression, the multiplication, division, and modulo operations are calculated first, followed by the subtraction and addition operations.

  • When all arithmetic operators in an expression have the same level of precedence, the order of execution is from left to right.

  • Expressions within parentheses take precedence over all other operations.

The following SELECT statement subtracts the part of the year-to-date sales that a sales person receives from their quota. The product of SalesQuota and CommissionPCT is calculated first, because the operator is multiplication. Next, the total is divided by 100. The result is subtracted from SalesYTD.

USE AdventureWorks;
GO
SELECT SalesPersonID, SalesYTD-SalesQuota* CommissionPCT/100
FROM Sales.SalesPerson;
GO

For clarity, you can use parentheses:

USE AdventureWorks;
GO
SELECT SalesPersonID, SalesYTD - ((SalesQuota * CommissionPCT)/100)
FROM Sales.SalesPerson;
GO

You can also use parentheses to change the order of execution. Calculations inside parentheses are evaluated first. If parentheses are nested, the most deeply nested calculation has precedence. For example, the result and meaning of the previous query can be changed if you use parentheses to force the evaluation of subtraction before multiplication:

USE AdventureWorks;
GO
SELECT SalesPersonID, ((SalesYTD-SalesQuota)* CommissionPCT)/ 100 
FROM Sales.SalesPerson;
GO