This tutorial aims to show everything useful about how to create calculations in SQL! From the simple:
SELECT
FilmName,
FilmOscarNominations-FilmOscarWins
FROM
tblFilm
to the complex:
SELECT
FilmName,
CASE
WHEN len(FilmSynopsis) % 100 = 0 THEN 'Short'
WHEN len(FilmSynopsis) % 100 = 1 THEN 'Medium'
ELSE 'Long'
END AS Verdict
FROM
tblFilm
All will be revealed!
The only thing this tutorial won't cover is date calculations (since these are amply covered in Andrew's separate tutorial on dates in SQL).
Simple Calculations
Let's start with the principles behind creating a calculated column. You already know - I hope - that you can include fields in SQL (most of the examples in this blog are taken from our evergreen movies database; I hope they'll make sense in their own right).
SELECT
FilmName,
FilmBoxOfficeDollars,
FilmBudgetDollars
FROM
tblFilm
In addition to columns taken straight from tables, you can also create formulae based upon them. For example, if you want to calculate the profit of each film for the above query, it's just the box office takings minus the budget:
SELECT
FilmName,
FilmBoxOfficeDollars,
FilmBudgetDollars,
FilmBoxOfficeDollars-FilmBudgetDollars
FROM
tblFilm
This will give the information required, although the column header just appears as (No column name):
The column header leaves a bit to be desired.
I'll look in more detail at arithmetical and string calculations later in this blog.
Giving Calculated Column Aliases
It's nearly always a good idea to give your shiny new calculated columns names:
SELECT
FilmName,
FilmBoxOfficeDollars-FilmBudgetDollars AS Profit
FROM
tblFilm
Note how you don't even have to include the box office takings and budget as output columns in the query in order to use them in a calculation.
This will give output similar to this:
This query shows that (at least according to Wise Owl data) Evan Almighty lost money. Never work with children or animals!
You can refer to calculated column aliases in ORDER BY clauses, but not in WHERE clauses or other columns. This is such an important point that it's worth showing examples of each in turn.
You Can Use Calculated Column Aliases in ORDER BY Clauses
These work fine. For example:
SELECT
FilmName,
FilmBoxOfficeDollars-FilmBudgetDollars AS Profit
FROM
tblFilm
ORDER BY
Profit DESC
Here we're ordering the data by the newly calculated Profit colum to get something like:
No doubt if Avatar was in our database that would be top ...
You Can't Use Calculated Column Aliases in WHERE Clauses
The following query will not work!
SELECT
FilmName,
FilmBoxOfficeDollars-FilmBudgetDollars AS Profit
FROM
tblFilm
WHERE
Profit <>
The error this will generate is:
You can't refer to the Profit column in a WHERE clause, as it's only just been calculated.
How to get round this? One way is to repeat the calculation:
SELECT
FilmName,
FilmBoxOfficeDollars-FilmBudgetDollars AS Profit
FROM
tblFilm
WHERE
FilmBoxOfficeDollars-FilmBudgetDollars <>
This works, but is messy and means you may make a mistake in one of the formula and get unexpected results. Another solution is to create and use a user-defined function called (say) fnProfit:
SELECT
FilmName,
dbo.fnProfit(FilmBoxOfficeDollars,FilmBudgetDollars) AS Profit
FROM
tblFilm
WHERE
dbo.fnProfit(FilmBoxOfficeDollars,FilmBudgetDollars) <>
This isn't much better, and requires that you know how to write scalar functions in SQL.
You Can't Use Calculated Columns in Other Calculations
By now, you probably wouldn't expect to be able to do this:
SELECT
FilmName,
FilmBoxOfficeDollars - FilmBudgetDollars AS Profit,
Profit/FilmRunTimeMinutes AS 'Profit per minute'
FROM
tblFilm
The problem is that you've only just calculated the Profit column, so you can't use it in another formula. You can use any of the solutions above to get round this - for example:
SELECT
FilmName,
FilmBoxOfficeDollars - FilmBudgetDollars AS Profit,
(FilmBoxOfficeDollars - FilmBudgetDollars)/
FilmRunTimeMinutes AS 'Profit per minute'
FROM
tblFilm
ORDER BY
'Profit per minute' DESC
Suddenly, Steven Spielberg is looking impressive:
Shrek and Steven Spielberg pretty much wrap up the top 5 places.
Perhaps you could save this query as Pointless and Dubious Statistic!
Using Built-In SQL Functions
There are a whole range of built-in SQL functions, covered in more detail later in this blog or in our separate blog on dates in SQL. You can get to them as shown below:
How to get to the LEN function, for example, which tells you how many characters there are in a string of text. Just expand:
Once you've found a function, you can expand it to see what arguments it takes, and what value it returns:
The LEN function takes a string expression, and returns a whole number (or integer).
Using Functions within SQL
If you want to use a function within SQL, you can either drag it in from the list above (or just type it in) to get:
SELECT
FilmName,
len(FilmSynopsis) AS 'Summary length'
FROM
tblFilm
ORDER BY
'Summary length' DESC
So the syntax of a function is:
FunctionName(First argument/paramter, ... , last argument/parameter)
The above function only takes one parameter or argument, but if it took more you would need to separate the arguments with commas, just like in Excel.
Converting or Casting Data
SQL is fussy about its data types (that's an understatement). You can see a full list of all the SQL data types here.
It's often useful to be able to convert something from one type of data to another. Examples might be:
Conversion | Example |
|---|
Integer to text | Including a number within a message string. |
Integer to decimal | Taking the average of a number. |
You can use the CONVERT function to convert dates, but for all other conversions it's easiest to use the CAST function. The syntax is:
CAST(Thing you're converting AS Data type you're converting it to)
If you're wondering why it's called CAST, techie programmers use this word to describe changing the data type of something (the word is also used by anglers, knitters, accountants and ophthalmologists, inter alia).
Here's an example:
SELECT
FilmName,
CAST(FilmRunTimeMinutes AS varchar(3)) +
' minutes' AS Length
FROM
tblFilm
Here's the output this would produce:
Because we're incorporating the (integer) film length in minutes into a text message, we need to first convert it to a string.
If you think this seems unnecessary, think again. Here's what you'd get if you didn't do it and ran this query:
SELECT
FilmName,
FilmRunTimeMinutes + ' minutes' AS Length
FROM
tblFilm
The result of running this query is this error message:
SQL sees the plus sign, realises that one of the two things is an integer, and assumes that the other one must be. It then fails to convert the word minutes to a number!
Now that we've covered the basics - creating calculations, using functions and casting data - we'll have a look at numerical and then (separately) string expressions.
