**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 **%**