BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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

- Formulae, Expressions and Calculations in SQL
- Arithmetic and Numerical Functions (this blog)
- Working with text (including string functions)
- CASE WHEN statement
- 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 |
---|---|

+ | Addition |

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

**B**rackets /**O**f**D**ivision /**M**ultiplication**A**ddition /**S**ubtraction

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 answer is now accurate:

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

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