579 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How to do calculations and expressions in TSQL Part two of a fivepart series of blogs 

You can use SQL to do everything from simple arithmetic through to complicated functions  this blog gives you the lowdown!
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. 
This part of my tutorial on calculated columns in TSQL looks at numerical formulae and expressions.
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.
As in most arithmetic, the order of operations in SQL is:
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.
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.
Excel has various trigonometric functions (to calculate sines, cosines, tangents, etc.), but this blog will restrict itself to nonspecialist 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 userfriendly SQL Server message!
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 nonintegers); 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.
Parts of this blog 


Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.