How to Receive Data from a Single Table - How to code arithmetic expressions
(Page 6 of 6 )
Figure 3-6 shows how to code
arithmetic expressions. To start, it summarizes the five
arithmetic operators you can use in this type of expression. Then, it presents three examples that illustrate how you use these operators.
The SELECT statement in the first example includes an arithmetic expression that calculates the balance due for an invoice. This expression subtracts the PaymentTotal and CreditTotal columns from the InvoiceTotal column. The resulting column is given the name BalanceDue.
When SQL Server evaluates an arithmetic expression, it performs the operations from left to right based on the order of precedence. This order says that multiplication, division, and modulo operations are done first, followed by addition and subtraction. If that’s not what you want, you can use parentheses to specify how you want an expression evaluated. Then, the expressions in the innermost sets of parentheses are evaluated first, followed by the expressions in outer sets of parentheses. Within each set of parentheses, the expression is evaluated from left to right in the order of precedence. Of course, you can also use parentheses to clarify an expression even if they’re not needed for the expression to be evaluated properly.
To illustrate how parentheses and the order of precedence affect the evaluation of an expression, consider the second example in this figure. Here, the expressions in the second and third columns both perform the same operations. When SQL Server evaluates the expression in the second column, it performs the multiplication operation before the addition operation because multiplication comes before addition in the order of precedence. When SQL Server evaluates the expression in the third column, however, it performs the addition operation first because it’s enclosed in parentheses. As you can see in the result set shown here, these two expressions result in different values.
Although you’re probably familiar with the addition, subtraction, multiplication, and division operators, you may not be familiar with the modulo operator. This operator returns the remainder of a division of two integers. This is illustrated in the third example in this figure. Here, the second column contains an expression that returns the quotient of a division operation. Note that the result of the division of two integers is always an integer. You’ll learn more about that in chapter 8. The third column contains an expression that returns the remainder of the division operation. If you study this example for a minute, you should quickly see how this works.
The arithmetic operators in order of precedence
Figure 3-6. How to code arithmetic expressions
* Multiplication
/ Division
% Modulo (Remainder)
+ Addition
- Subtraction
A SELECT statement that calculates the balance due
SELECT InvoiceTotal, PaymentTotal, CreditTotal,
InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue
FROM Invoices
A SELECT statement that uses parentheses to control the sequence of operations
SELECT InvoiceID,
InvoiceID + 7 * 3 AS OrderOfPrecedence,
(InvoiceID + 7) * 3 AS AddFirst
FROM Invoices
A SELECT statement that uses the modulo operator
SELECT InvoiceID,
InvoiceID / 10 AS Quotient,
InvoiceID % 10 AS Remainder
FROM Invoices
Description
- Unless parentheses are used, the operations in an expression take place from left to right in the order of precedence. For arithmetic expressions, multiplication, division, and modulo operations are done first, followed by addition and subtraction.
Whenever necessary, you can use parentheses to clarify or override the sequence of operations. Then, the operations in the innermost sets of parentheses are done first, followed by the operations in the next sets, and so on.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter three of the book Murach's SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 9 
