Calculated Fields

Contact Us or call 1-877-932-8228
Calculated Fields

Calculated Fields

Calculated fields are fields that do not exist in a table, but are created in the SELECT statement. For example, you might want to create FullName from FirstName and LastName.

Concatenation

Concatenation is a fancy word for stringing together different words or characters. SQL Server, Oracle and MySQL each has its own way of handling concatenation. All three of the code samples below will return the following results:

In SQL Server, the plus sign (+) is used as the concatenation operator.

Code Sample:

AdvancedSelects/Demos/Concatenate-SqlServer.sql
-- Select the full name of all employees. SQL SERVER.

SELECT FirstName + ' ' + LastName
FROM Employees;

In Oracle, the double pipe (||) is used as the concatenation operator.

Code Sample:

AdvancedSelects/Demos/Concatenate-Oracle.sql
-- Select the full name of all employees. Oracle.

SELECT FirstName || ' ' || LastName
FROM Employees;

MySQL does this in yet another way. There is no concatenation operator. Instead, MySQL uses the CONCAT() function .

Code Sample:

AdvancedSelects/Demos/Concatenate-MySQL.sql
-- Select the full name of all employees. MySQL.
SELECT CONCAT(FirstName, ' ', LastName)
FROM Employees;

Note that concatenation only works with strings. To concatenate other data types, you must first convert them to strings.

Mathematical Calculations

Mathematical calculations in SQL are similar to those in other languages.

Mathematical Operators
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulus

Code Sample:

AdvancedSelects/Demos/MathCalc.sql
/*
If the cost of freight is greater than or equal to $500.00,
it will now be taxed by 10%. Create a report that shows the
order id, freight cost, freight cost with this tax for all
orders of $500 or more.
*/

SELECT OrderID, Freight, Freight * 1.1
FROM Orders
WHERE Freight >= 500;

The above SELECT statement will return the following results:

Aliases

You will notice in the examples above that the calculated columns have the header "(No column name)". The keyword AS is used to provide a named header for the column.

Note: you cannot use aliases in a WHERE clause!

Code Sample:

AdvancedSelects/Demos/Alias.sql
SELECT OrderID, Freight, Freight * 1.1 AS FreightTotal
FROM Orders
WHERE Freight >= 500;

As you can see, the third column now has the title "FreightTotal".

Next