Why should you use calculation fields? As a rule, the information in the database is presented in the form of separate fragments, because it is easier to structure the data and operate on it. However, we will often need to use not individual pieces of data, but already combined and processed information. For example, it is often necessary to combine the first and last names of customers, to combine address elements that are in different columns of the table, to process text and individual words, letters and symbols, to sum up the total cost of the purchase, to display statistics on the information that is in the database. Data is usually stored in separate "chunks", which requires additional processing on the side of the client program. However, it is possible to receive already processed information using a DBMS. It is in this case that calculation fields help. They are automatically created when the query is executed and have the appearance and properties of ordinary columns that are already present in the table. The only difference is that there are no physically calculated fields, so they do not take up additional space in the DB, but temporarily exist in the "random memory" of the DBMS. The advantage of performing operations on the DBMS side is the speed of data processing.
One way to use calculated fields is to perform mathematical operations on selected data. Let's take an example of how this happens, using our Sumproduct table again. Suppose you need to calculate the average purchase price of each product. To do this, you need to redistribute the Amount column to Quantity (quantity):
Run SQLSELECT DISTINCT Product, Amount/Quantity
FROM Sumproduct
As you can see, the DBMS selected all product names and displayed their average cost in a separate column that was created during the execution of the query. You can also notice that we used an additional operator DISTINCT, which we need to display unique product names (without it we would have duplicate records).
In the previous example, we calculated the average purchase price of each product and displayed the value in the calculation column. However, in the future, it will be inconvenient for us to refer to this field, since its name is uninformative for us (the DBMS gave the name of the field - Expr1001). However, we can name the field independently by specifying its name in the query in advance, i.e. giving an alias. Let's rewrite the previous example and specify an alias for the calculated field:
Run SQLSELECT DISTINCT Product, Amount/Quantity AS AvgPrice
FROM Sumproduct
We see that our calculation field has received its own name AvgPrice. For this, we used the operator AS, after which we specified the name we needed. It should be noted that only basic mathematical operations are supported in SQL: addition (+), subtraction (-), multiplication (*), division (/). You can also use round brackets to change the sequence of operations.
Aliases are often used not only to name calculation fields, but also to rename active ones. This may be necessary if the active field has a long name or the name is not informative enough.
In addition to mathematical operations, we can also combine text and display it in a separate field. Let's consider how you can glue (concatenate) text. To connect text from different columns in MS Access, the plus operator (+) is used, for example:
Run SQLSELECT Month + ' ' + Product AS NewField, Quantity
FROM Sumproduct
In this example, we combined the values in two columns and output the result to a new field NewField.