Performing Mathematical Calculations

Another frequent use for calculated fields is performing mathematical calculations on retrieved data. Let's take a look at an example. The Orders table contains all orders received, and the OrderItems table contains the individual items within each order. The following SQL statement retrieves all the items in order number 20008:

SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;

prod_id       quantity     item_price
---------- ----------- ---------------------
RGAN01        5            4.9900
BR03          5            11.9900
BNBG01        10           3.4900
BNBG02        10           3.4900
BNBG03        10           3.4900

The item_price column contains the per unit price for each item in an order. To expand the item price (item price multiplied by quantity ordered), you simply do the following:

SELECT prod_id,
        quantity,
        item_price,
        quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

prod_id     quantity     item_price     expanded_price
----------  -----------  ---------------------       ---------------------
RGAN01      5            4.9900         24.9500
BR03        5            11.9900        59.9500
BNBG01      10           3.4900         34.9000
BNBG02      10           3.4900         34.9000
BNBG03      10           3.4900         34.9000

The expanded_price column shown in the output above is a calculated field; the calculation is simply quantity*item_price. The client application can now use this new calculated column just as it would any other column.

SQL supports the basic mathematical operators listed in Table 7.1. In addition, parentheses can be used to establish order of precedence. Refer to Lesson 5, "Advanced Data Filtering," for an explanation of precedence.