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 20005:

• Input

SELECT prod_id, quantity, item_price FROM orderitems WHERE order_num = 20005;

• Output

+---------+----------+------------+ | prod_id | quantity | item_price | +---------+----------+------------+ | ANV01   |       10 |       5.99 | | ANV02   |        3 |       9.99 | | TNT2    |        5 |      10.00 | | FB      |        1 |      10.00 | +---------+----------+------------+

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:

• Input

SELECT prod_id,        quantity,        item_price,        quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

• Output

+---------+----------+------------+----------------+ | prod_id | quantity | item_price | expanded_price | +---------+----------+------------+----------------+ | ANV01   |       10 |       5.99 |          59.90 | | ANV02   |        3 |       9.99 |          29.97 | | TNT2    |        5 |      10.00 |          50.00 | | FB      |        1 |      10.00 |          10.00 | +---------+----------+------------+----------------+

• Analysis

The expanded_price column shown in the previous output 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.

MySQL supports the basic mathematical operators listed in Table 10.1. In addition, parentheses can be used to establish order of precedence. Refer to Chapter 7, "Advanced Data Filtering," for an explanation of precedence.

Table 10.1. MySQL Mathematical Operators

Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division

Tip

How to Test Calculations SELECT provides a great way to test and experiment with functions and calculations. Although SELECT is usually used to retrieve data from a table, the FROM clause may be omitted to simply access and work with expressions. For example, SELECT 3 * 2; would return 6, SELECT Trim(' abc '); would return abc, and SELECT Now() uses the Now() function to return the current date and time. You get the ideause SELECT to experiment as needed.