SQL Basics - Calculated Fields and Aliases

Very often you will find that you need to display data not in it's original form. Such as totals, averages, or multiples, or just reformatted. We can usually do it with the application we're using, but it is more efficient to do it using SQL. What happens is that SQL creates a virtual column containing your calculated field.

Here are some of the most used operators.

CONCATENATION

This operator takes two or more strings  and connects them into one long string. For this we use the '+' symbol in MS Access and some others. In some programs '||' is used (two straight up lines.)

EXAMPLE

SELECT child_name + child_eyes FROM TeethEyes;

Not extremely readable, is it? Here's what we can do to help that

SELECT child_name + ' has ' + child_eyes + ' eyes.' FROM TeethEyes;

That's a little better.What we did is add the name, ' has ', which is a space, the word has ans a space. Then we added the eye color, a space and eyes. Pretty simple.

MATHEMATICAL FORMATTING

You can also use the '+' symbol on numbers to add them together. For that matter, you can use all 4 regular operators (+,-,*,/).

EXAMPLE

SELECT (quantity*item_price) FROM OrderItems WHERE order_num=20005

That's the number of items ordered multiplied by the price of the items, for order number 20005. If we want to get the total price for the order we would have to use SUM(), which we'll hopefully learn more about in another lesson.

SELECT SUM(quantity*item_price) FROM OrderItems WHERE order_num=20005

If you noticed, the column name in the past few pictures was Expr1000. That's the name automatically given to the virtual column by SQL. Sometimes we need to name the column so that our application can work with it. For this we use aliases, by adding the AS parameter.

SELECT SUM(quantity*item_price) AS order_total FROM OrderItems WHERE order_num=20005

As you see, now our virtual column has a name order_total, and as far as our application knows, it's a regular real column to work with.