Calculated Fields

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.

Concatenating Fields

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;

Using aliases

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;