You can use the SELECT statement expressions to perform calculations on your table’s columns. These calculations are called expressions. In this lesson we are going to look at expressions to perform simple calculations. Keep in mind this is just the beginning! There are many built-in functions you can use to build more complex expressions, but for today’s lesson let’s keep it simple and focus on some basic arithmetic.
All of the examples in this tutorial are based on PizzaDB. You can get the script to build the PizzaDB here.
In this example we’re going to work with the Product table to calculate new prices.
Example
What would prices be, if we increased them by a 1 dollar?
For this example you can see we use an expression as our last column. It takes the price adds one dollar to the amount. Since this is in the query’s column list, it performs this calculation for every row in the result.
You may have notice there is no meaningful column name for our calculation. However, we can add one using a column alias. This is just a fancy way of saying rename the column.
Example
Rename the calculated column to NewPrice so it is easier to read and use.
You can see the alias NewPrice is placed after the column. You can use the optional keyword “as” to separate them.
Here is the statement with “as”
select ProductID, ProductName, Price, Price+1 as NewPrice
from Product
Here is the statement without it.
select ProductID, ProductName, Price, Price+1 NewPrice
from Product
Either variation works.
Syntax
Here is the syntax for using an expression in the SELECT Statement
SELECT column1, column2, expression as alias, ..
FROM TableName
This is a simplified view of the syntax, so there are a couple of items you should know:
- First, one or more expressions can be in the select statement.
- The expressions can be in any order, infact, they can be at the beginning of the column list.
- You can alias columns just as you can an expression.
You can use these operators in your expressions:
Operator | Description | Example |
---|---|---|
+ | Addition | column1 + column2 |
– | Subtraction | column1 – column2 |
* | Multiplication | column1 * column2 |
/ | Division | column1 / column2 |
( ) | Parenthesis to establish precedence | (1 + column1) / column2 |
Using Multiple Expressions in Select
in this example let use several expressions to show price differences resulting from a 5% increase in prices.
We’ll add two expressions:
- NewPrice = Price * 1.05 — this is a 5% increase
- PriceDifference = Price * .05 — this is the difference
Did you notice I place the expressions on a separate line? That’s to make it easier to read. As long as the columns and expression are separated by commas (,) , you place on the same or separate lines; it’s your choice!
Exercise
Write a query to list the ProductID, ProductName, Price, and NewPrice
For the Price increase use the following formula: (1 + .08) * price