SELECT prod_id, quantity, item_price FROM orderitems WHERE order_num = 20005;
+---------+----------+------------+ | 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:
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
+---------+----------+------------+----------------+ | 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 | +---------+----------+------------+----------------+
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 following table. In addition, parentheses can be used to establish order of precedence.
MySQL Mathematical Operators
Operator |
Description |
---|---|
|
Addition |
|
Subtraction |
|
Multiplication |
|
Division |
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.
In this tutorial, you learned what calculated fields are and how to create them. We used examples demonstrating the use of calculated fields for both string concatenation and mathematical operations. In addition, you learned how to create and use aliases so your application can refer to calculated fields.