Understanding Calculated Fields
Calculated field are created on the fly and do not exist in the database. We might want to retrieve the first name and last name columns together but in the database they are stored separately, so we can use a calculated field to join them together and return them as a single column.
We use the + operator to concatenate two columns together. In the following sql statement we have added parantheses to the county retrieved
SELECT vend_name + ' (' + vend_country + ')' FROM Vendors ORDER BY vend_name;
We can also use the || operator for concatenation.
SELECT vend_name || ' (' || vend_country || ')' FROM Vendors ORDER BY vend_name;
When using a calculated field you will notice the new column does not have a meaningful name. We can create an alias for the column. We use the AS keyword immediately after the calculated field.
In the following statement we will give the calculated field a name called vendor_title :
SELECT vend_name || ' (' || vend_country || ')' AS vend_title FROM Vendors ORDER BY vend_name;
You can also skip the AS and just put the column name after the calculated and it will work fine, but its recommend that you use the AS keyword.
Performing Mathematical Calculations
We can also use calculated fields in performing mathematical calculations. In the following statement we use the calculated field to calculate the expanded price :
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;