How to do calculations and expressions in T-SQL
Part one of a five-part series of blogs

You can use SQL to do everything from simple arithmetic through to complicated functions - this blog gives you the low-down!

  1. Formulae, Expressions and Calculations in SQL (this blog)
  2. Arithmetic and Numerical Functions
  3. Working with text (including string functions)
  4. CASE WHEN statement
  5. Dealing with nulls - ISNULL, COALESCE and CASE

This blog is part of our full SQL online tutorial.  You can learn how to write SQL on a Wise Owl SQL classroom course if you're in the UK.

Posted by Andy Brown on 11 December 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Formulae, Expressions and Calculations in SQL

This blog aims to show everything useful about how to create calculations in SQL! From the simple:

-- show film names

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 two things this blog won't cover are date calculations (since these are amply covered in Andrew's separate blog on dates in SQL) and computed columns (also the subject of a separate blog).

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).

-- show film names with financial info

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):

No column header heading

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:

List of films with profit column

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:

-- show films with most profitable first

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:

Films with most profitable first

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!

-- show loss-making films

SELECT

FilmName,

FilmBoxOfficeDollars-FilmBudgetDollars AS Profit

FROM

tblFilm

WHERE

Profit <>

The error this will generate is:

Invalid column name error message

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:

-- show loss-making films

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:

-- show loss-making films

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.

A nice solution is to use a common table expression, but this is meant to be a fairly basic SQL tutorial for now!

You Can't Use Calculated Columns in Other Calculations

By now, you probably wouldn't expect to be able to do this:

-- show profit per minute

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:

-- show profit per minute for every film

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:

Top films by profit per minute

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:

Example function - LEN

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 parameters

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:

-- show length of film synopses

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:

-- show length of films

SELECT

FilmName,

CAST(FilmRunTimeMinutes AS varchar(3)) +

' minutes' AS Length

FROM

tblFilm

Here's the output this would produce:

The length of films as text

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:

]Conversion failed - message 245

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. 

This blog has 0 threads Add post