Wednesday, August 7, 2013

Calculating a Remainder in SQL

When doing math problems using SQL, it is a good idea to consider what type of data you are working with.  In SQL, you will often deal with a data type called Integer.  With integers, if you do any division, all your answers will come out in whole numbers (i.e. you won't get any decimal numbers back).  For example, if you run the following in SQL, the answer will be 2 instead of 2.5:

select 5 / 2

Clearly you can start to lose data if you are expecting decimal places while dividing integers.  It is often better to convert your integers to a data type that allows for decimals.  For example, you can convert your integer data types to numeric and then perform the division.

select cast(5 as numeric(2,1)) / 2

If you don't want to cast your data to another data type because of possible loss of data, you can also use the Modulo operator (sometimes called Modulus in other programming languages).  To use the Modulo operator, replace the divide sign (/) with the percent sign (%).  Using the same example from above, we will use the Modulo operator:

select 5 % 2

The answer to the above would then be 1, which is the remainder of 5 / 2.

We could then display our data using the following query:

select ' 5 / 2 is ' + cast(5 / 2 as varchar) + ' with a remainder of ' + cast(5 % 2 as varchar)

Which gives us the results of

 5 / 2 is 2 with a remainder of 1

To read more on Modulo, go here: Modulo

No comments:

Post a Comment