COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
If you found this blog useful and you’d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

How to do calculations and expressions in T-SQL
Part two 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
2. Arithmetic and Numerical Functions (this blog)
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.

# Arithmetic and Numerical Functions

This part of my tutorial on calculated columns in T-SQL looks at numerical formulae and expressions.

## Mathematical Symbols (Adding, Subtracting, Multiplying and Dividing)

The main symbols that you can use in SQL are the standard ones:

Symbol What it does
- Subtraction
* Multiplication
/ Division

Thus the following SQL would show for each film in a database the difference between the number of Oscar nominations and the number of Oscars won:

SELECT

FilmName,

FilmOscarWins AS Wins,

FilmOscarNominations AS Nominations,

-- calculate the difference

FilmOscarNominations - FilmOscarWins AS 'Oscar shortfall'

FROM

tblFilm

ORDER BY

'Oscar shortfall' DESC

In the standard Wise Owl database, Gangs of New York tops this list, with 10 nominations and no wins.  At the other end of the scale, kudos to The Matrix for winning 4 out of the 4 Oscars for which it was nominated.

## Order of Symbols - BODMAS

As in most arithmetic, the order of operations in SQL is:

• Brackets / Of
• Division / Multiplication

This is sometimes remembered by its acronym BODMAS.  The difference is shown by the following example:

-- this query shows the difference

SELECT

2 + 3 * 5 as 'Without brackets',

(2 + 3) * 5 as 'With brackets'

Here is what you'd get if you ran this query: Without brackets, the multiplication is done before the addtion.

## The % Modulus Operator

If you want to find out the remainder when you divide one integer by another use the modulus operator, or %.  The following query, for example, would show how long each film lasted in hours and minutes:

-- show film duration as hours and minutes

SELECT

FilmName,

FilmRunTimeMinutes AS Length,

(FilmRunTimeMinutes - FilmRunTimeMinutes % 60)/60 AS Hrs,

FilmRunTimeMinutes % 60 AS Mins

FROM

tblFilm

Here is what this query would show: The results of running this query. For each film we see the whole number of hours, plus remaining minutes.

For example, Jurassic Park lasts 127 minutes.  The remainder when 127 is divided by 60 is 7, which gives the Mins column, from which the Hrs column follows.

## Built-in Mathematical Functions

Excel has various trigonometric functions (to calculate sines, cosines, tangents, etc.), but this blog will restrict itself to non-specialist mathematical functions.  These are shown in the following SQL example:

-- show results of various functions

SELECT

Abs(-3.7) AS 'Absolute value',

Ceiling (3.7) AS 'Upper limit',

Floor(3.7) AS 'Lower limit',

Power(2,3) AS 'Power eg',

Round(3.14159,2) AS 'Rounding',

Sign(-3.7) AS 'Sign eg',

SqRt(2) AS 'Square root',

Square(2) AS 'Square eg'

Here's the output from running this query: The results from running the query above.

The functions used are as follows:

Function What it does
Abs Takes the absolute value of a number (its positive amount).
Ceiling, Floor Shows the next highest or lowest integer for a number.
Power Raises one number to the power of another.
Round Rounds a number to a number of decimal places.
Sign Returns -1 if a number is negative, 0 (if zero) or +1 (if positive).
SqRt, Square Takes the square root of a number or squares it.

If you're wondering what happens if you take the square root of a negative number, you get the error message An invalid floating point operation occurred - a typically user-friendly SQL Server message!

## The Importance of Converting Numbers

SQL is nothing if not fussy about its data types.  Consider the following query:

SELECT

FilmName,

FilmRunTimeMinutes,

-- show length of film in hours

FilmRunTimeMinutes / 60 AS Hours

FROM

tblFilm

What would you expect this to show for the hours?  If a film lasted 75 minutes, will you see 1 or 1.25 hours?  The answer is that because the FilmRunTimeMinutes column is an integer, so will the answer be: The hours shown are all integers (whole numbers).

One way round this would be to convert the input values into float data types (ie non-integers); the answer will then automatically be returned as the same data type:

SELECT

FilmName,

FilmRunTimeMinutes,

-- show length of film in hours

CAST(FilmRunTimeMinutes AS float)/ 60 AS Hours

FROM

tblFilm The data type of the answer is now float, not int.

You'll frequently find yourself having to cast integers as floating point numbers before using them in operations involving division, such as averaging.

So much for numbers - let's now look at text.