How to Use Calculated Fields Effectively in SQL Databases

Example of Calculation Code

Here’s an example of a SQL query that performs a mathematical calculation to calculate the total price of an item in an order:

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

Code Sample Analysis

prod_id, quantity, item_price: These columns are taken from the order items table, which contains information about the product, the quantity ordered, and the price per unit.

quantity *  item_price AS expanded_price: Here, we do the calculation by multiplying the quantity by item_price (price per unit). The result of this calculation is named alias expanded_price, which will appear as a new column in the query results.

WHERE order_num = 20005: This condition ensures that only items from the order with the number 20005 are picked.

Output

The result of this query will generate a table with the following information:

+---------+----------+------------+---------------+
| 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         |
+---------+----------+------------+---------------+

In that output, the expanded_price column shows the total price for each item based on the quantity ordered.

Latest Articles

Related Articles