Calculated Columns in SQL Server

Adding a Calculation to a Query

SELECT 
    bank_transaction_pk AS TransNumber,
    account_number_fk AS AcctNumber,
    transdate AS TransDate,
    Transdescription AS TransactionDesc,
    amount as TransAmt,
    amount + 10 AS [Amount Plus Fee]
FROM
    bank_transaction

The aliased column called "Amount Plus Fee" in the sample query above is a calculated column.  It adds a $10 fee to each Transaction amount in the bank_transaction table.  Other operators such as minus - , multiply * and divide can also be used to create a calculation.

1.) Add another column to the query above and call it High Fee.  Create a calculation for this column that multiplies the amount field by 20.  

Using Calculations to Sort Queries

You can sort your query results by a calculated column.  The sample query below sorts the "Amount Plus Fee" column in descending order.

SELECT 
bank_transaction_pk AS TransNumber,
account_number_fk AS AcctNumber,
transdate AS TransDate,
Transdescription AS TransactionDesc,
amount as TransAmt,
amount + 10 AS [Amount Plus Fee]
FROM
bank_transaction
ORDER BY [Amount Plus Fee] DESC

2.) Sort the the last query you created from question 2 above, by the High Fee column.

3.) Create yet another calculated column called "Divided by Two" and divide the amount by two.  Sort the results by this column in descending order.

Using Calculations in Criteria

The results of a calculation can be used in a WHERE clause to add criteria to a query.  It works a bit different from placing the alias calculated column in an ORDER BY clause.  An alias cannot be used in a WHERE clause, instead the calculation must be repeated here.  Observe the WHERE clause in the example below, it returns records where the "Amount Plus Fee" calculated column is less than 20.  Wrapping the calculation in a set of round brackets helps with the readability of your query

SELECT 
bank_transaction_pk AS TransNumber,
account_number_fk AS AcctNumber,
transdate AS TransDate,
Transdescription AS TransactionDesc,
amount as TransAmt,
amount + 10 AS [Amount Plus Fee],
amount / 2 AS [Divided by Two]
FROM
bank_transaction
WHERE
(amount + 10) < 20
ORDER BY [Amount Plus Fee] DESC

4.) Return all transactions where the High Fee column is = 8633.8

Cautions with using Data Types

The data type you are working with can be the cause of error when working with SQL Server.  This is because SQL Server relies on you to explicitly convert data types.  In the sample query below we introduce a calculated column called "Age in Two" which divides all peoples ages by 2.  The data type for the age column is integer which holds whole numbers.  When divided by 2, which is another whole number the result of the calculation is also a whole number.  Which does not return accurate results.

SELECT
firstName,
residence,
age,
age / 2 AS [Age in Half]
FROM
person

5.) What is the result for the "Age in Half" calculated column for the following values: 57, 17, 45, and 95?

By changing one of the values from an integer or whole number to a decimal we achieve a more accurate result from our query.  We will explore functions to make sure we are always working with the correct data type later in the tutorial.

SELECT
    firstName,
    residence,
    age,
    age / 2.0 AS [Age in Half]
FROM
    person

6.) What is the result for the "Age in Half" calculated column for the following values: 57, 17, 45, and 95 in the modified sample query above?

Simple Examples

The following query makes use of a number of different variations of calculated columns.  Take a look at the "Remainder" column, it introduces a new operator "%" referred to as modulo or mod for short.  The mod operator returns the remainder of a division.  In the example below it returns the remainder of the amount column divided by 2.

SELECT 
bank_transaction_pk AS TransNumber,
account_number_fk AS AcctNumber,
transdate AS TransDate,
Transdescription AS TransactionDesc,
amount as TransAmt,
amount + 10 AS [Amount Plus Fee],
(amount + 10) - 2 [Two Dollars Off], 
amount % 2 [Remainder]
FROM
bank_transaction

7) Create three calculated columns for the persons table that makes use of the following operators and %