MariaDB Crash Course - Ben Forta [30]
Input
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_
title
FROM vendors
ORDER BY vend_title;
Output
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------+
Analysis
The SELECT statement itself is the same as the one used in the previous code snippet, except that here the calculated field is followed by the text AS vend_title. This instructs SQL to create a calculated field named vend_title containing the results of the specified calculation. As you can see in the output, the results are the same as before, but the column is now named vend_title and any client application can refer to this column by name, just as it would to any actual table column. Indeed, the ORDER BY itself uses the calculated vend_title.
* * *
Tip: Other Uses for Aliases
Aliases have other uses, too. Some common uses include renaming a column if the real table column name contains illegal characters (for example, spaces) and expanding column names if the original names are either ambiguous or easily misread.
* * *
* * *
Note: Derived Columns
Aliases are also sometimes referred to as derived columns, so regardless of the term you run across, they mean the same thing.
* * *
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.
MariaDB 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 MariaDB Mathematical Operators
* * *
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 idea—use SELECT to experiment as needed.
* * *
Summary
In this chapter, you learned what calculated fields are and how to create them. We used examples demonstrating the use of calculated fields for both string concatenation