The following arithmetic operators are supported:
| Symbol | Operation |
|---|---|
| + | Addition |
| - | Subtraction |
| / | Division |
| * | Multiplication |
| % | Modulo |
The arithmetic operators that perform addition, subtraction, division, and multiplication can be used on any numeric column ( int, smallint, tinyint, decimal, numeric, float, real, money, or smallmoney). The modulo operator cannot be used on money columns. (A modulo is the integer that remains after two integers are divided. For example, 21 % 9 = 3, because 21 divided by 9 equals 2, with a remainder, or modulo, of 3.)
Certain arithmetic operations can also be performed on datetime and smalldatetime columns using the date functions. For details, see Functions in the Microsoft SQL Server Transact-SQL Reference.
All the arithmetic operators can be used in the select list with column names and numeric constants in any combination. The following example uses the multiplication operator to show a projected sales increase of 100 percent for all the books in the titles table:
SELECT title_id, ytd_sales, ytd_sales * 2 FROM titles title_id ytd_sales ytd_sales * 2 -------- ----------- ----------- BU1032 4095 8190 BU1111 3876 7752 BU2075 18722 37444 BU7832 4095 8190 MC2222 2032 4064 MC3021 22246 44492 MC3026 (null) (null) PC1035 8780 17560 PC8888 4095 8190 PC9999 (null) (null) PS1372 375 750 PS2091 2045 4090 PS2106 111 222 PS3333 4072 8144 PS7777 3336 6672 TC3218 375 750 TC4203 15096 30192 TC7777 4095 8190 (18 row(s) affected)
Notice the NULL entries in the ytd_sales and computed columns. Null values have no explicitly assigned values. When you perform any arithmetic operation on a null value, the result is NULL. (You can use the system function ISNULL() to substitute a real value for NULL. For details, see the Functions topic in the Microsoft SQL Server Transact-SQL Reference.)
To give the computed column a heading (say proj_sales):
SELECT title_id, ytd_sales, proj_sales = ytd_sales * 2 FROM titles
The column from which the computed column is generated does not have to appear in the select list. The ytd_sales column, for example, is shown in the preceding queries only for comparison with the ytd_sales * 2 column. To see just the computed values:
SELECT title_id, ytd_sales * 2 FROM titles
You can use arithmetic operators to perform computations involving one or more columns. The use of constants in arithmetic expressions is optional, as shown in this example:
SELECT title_id, ytd_sales * price |
||
FROM titles |
||
Computed columns can come from more than one table. The following query computes the product of the number of copies of a psychology book sold (the qty column from the sales table) and the price of the book (the price column from the titles table):
SELECT titles.title_id, stor_id, qty * price |
FROM titles, sales |
WHERE titles.title_id = sales.title_id |
AND titles.title_id = 'PS2091' |
The WHERE clause in this example is a join condition that specifies the connection between the tables listed in the FROM clause. For details about working with multitable queries, see Using Joins to Create Multitable Queries and Using Subqueries.
Mixed-mode arithmetic means performing arithmetic operations on values of different datatypes (for example, int smallint). The hierarchy of datatypes, which comes from the rank ordering of the values of the datatype codes, determines the datatype of the result. In a mixed-mode expression, the lower datatype is converted internally to the higher type. The only exception to this rule is when converting money and other number datatypes such as float, real, decimal, and numeric; money always takes precedence, even though its type is lower. The hierarchy of datatypes is shown in the systypes system table.
If, for example, sales.qty (a smallint) is multiplied by roysched.royalty (an int), the resultant datatype is int. This is because the smallint datatype code has a value of 52 and the int datatype code has a value of 56.
To obtain a list of datatypes with their type value codes from the systypes table, use the following query. Select the name of the datatype and its type code, and then use the ORDER BY statement to display the hierarchy:
SELECT name, type FROM systypes ORDER BY type
The results of this query differ if you have user-defined datatypes.
When there is more than one arithmetic operator in an expression, multiplication, division, and modulo are calculated first, followed by subtraction and addition. When all arithmetic operators in an expression have the same level of precedence, the order of execution is left to right. Expressions within parentheses take precedence over all other operations.
For example, the following SELECT statement subtracts the part of the year-to-date sales that the author receives (sales * author's royalty percentage / 100) from the total sales. The final result is the amount of money the publisher receives.
The product of ytd_sales and royalty is calculated first because the operator is multiplication. Next, the total is divided by 100. Then this result is subtracted from ytd_sales:
SELECT title_id, ytd_sales - ytd_sales * royalty / 100 FROM titles
For clarity, you can use parentheses. The following query has the same meaning and gives the same results as the preceding one:
SELECT title_id, ytd_sales - ((ytd_sales * royalty) / 100) FROM titles
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 preceding query can be changed if you use parentheses to force the evaluation of subtraction before multiplication:
SELECT title_id, (ytd_sales - ytd_sales) * royalty / 100 FROM titles