Click here to Skip to main content
15,868,141 members
Articles / Programming Languages / SQL

Introduction to SQL Server’s Mathematical Functions

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
4 Jan 2016MIT6 min read 11.3K   3  
Introduction to SQL Server’s Mathematical Functions

Math Rocks!

SQL Server includes many mathematical functions you can use to perform business and engineering calculations. Many of these aren’t used in typical day-to-day operations; however, there are several commonly used functions we’ll cover.

If you are not familiar with SQL functions, then I would recommend starting with the Introduction to SQL Server Built-In Functions.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.

Introduction to SQL Server’s Mathematical Functions

There are many mathematical functions within SQL Server at your disposal. I would generally categorize them as the following:

  • Scientific and Trig Functions
  • Rounding Functions
  • Signs
  • Random Numbers

All of the functions are listed on this MSDN page. I would recommend visiting that page to learn about each function.

Rather than reiterate that material, we’ll focus on the functions I’ve seen in commonly used in business.

In the following table, I categorized the functions and color coded them. The color code corresponds to the likelihood you will use that particular function in a business environment: Green is most likely used, and red less so.

This isn’t a strict scale, and all functions are awesome (I’m an engineer and love math), but I wanted a way to help you winnow down the field to those most relevant.

Here is my attempt:

Most commonly used sqlserver mathematical functions

SQLServer Mathematical Functions
Green – Most Commonly Used

In the remainder of the article, we’ll go over the functions highlighted in green. Along the way, I’ll try to point out some interesting ideas and uses about the functions. Please read every section, as there is always something new to learn.

Scientific and Trig Functions

PI

To be honest, PI really shouldn’t be on this list, but let’s face it, PI is cool. And now that you have the function, there is no need to memorize this fabled constant used to calculate circumference and area of circles.

SQL
SELECT 2 * PI() * 10

Returns the circumference of a circle with radius 10.

SQRT

SQRT stands for square root. This is a relatively common function. Most of us have run across it when working with areas.

The SQRT function accepts any numeric value that can be implicitly converted to a float datatype. The result is also float.

To illustrate:

SQL
SELECT SQRT(25)

Returns 5

Imaginary numbers aren’t supported, so trying to calculate the square root of a negative number won’t work. You’ll get the following error:

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

SQUARE

SQUARE is a short hand way to multiply an expression by itself.

Do you remember Pythagoras theorem from high school as A<sup>2</sup> + B<sup>2</sup> = C<sup>2</sup>?

We could write this in SQL as:

SQL
SELECT SQUARE(A) + SQUARE(B) as CSquared

And now that we know about SQRT, we can calculate C as:

SQL
SELECT SQRT(SQUARE(A) + SQUARE(B)) as C

One point to get out of this is that you can nest functions. The result of one function can be pumped into the next.

Rounding Functions

CEILING and FLOOR

The CEILING and FLOOR functions are useful when you have a float or decimal value and you need to find the next lowest or highest integer.

CEILING and FLOOR functions explained

Explanation of CEILING and FLOOR

The CEILING is the next highest integer value; whereas, floor is the next lowest.

CEILING and FLOOR have a practical use when you’re working with discrete quantities and averages.

For instance, let’s assume the sales manager is having a sales convention for all the sales people. They want to rent cars to be able to get around town. Assuming each car holds 4 people, how many cars does he need to rent for the business trip?

To figure this out, we can take the number of sales people and divide by four:

SQL
SELECT COUNT(*) / 4.0 as NumberCars
FROM   Sales.SalesPerson

This result is 4.25 cars. As you know, you cannot rent nor drive a quarter of a car!

To get around this, we need to round up to the nearest whole car. We can use CEILING to do so. Here is the query to use:

SQL
SELECT CEILING(COUNT(*) / 4.0) as NumberCars
FROM   Sales.SalesPerson

Also, did you notice I used 4.0 rather than 4 in the calculation? This is to ensure the result is a float; otherwise, the result is returned as an integer, and would have been implicitly converted to an integer value of 4.

ROUND

The ROUND function is used to round a value to the nearest specified decimal place. The general format for ROUND is:

SQL
ROUND(value, number of decimal places)

Thus:

SQL
SELECT ROUND(5.153745,0)

returns 5.000000 as 5.1 is rounded to 5.

SQL
SELECT ROUND(5.153745,1)

returns 5.200000 as 5.15 is rounded to 5.2 and

SQL
SELECT ROUND(5.153745,2)

returns 5.150000 as 5.153 is rounded to 5.15.

In all cases results datatype is the same as the input value.

Let’s look at another example, suppose the sales manager asks for sales order detail information. You provide him with the following query:

SQL
SELECT SalesOrderID,
       SalesOrderDetailID,
       OrderQty,
       UnitPrice,
       UnitPriceDiscount,
       LineTotal
  FROM Sales.SalesOrderDetail

He says, “That’s great, but can you round the line totals to the nearest penny?”

How would you go about doing that? Here is a query you can use with the ROUND function to accomplish that task:

SQL
SELECT SalesOrderID,
       SalesOrderDetailID,
       OrderQty,
       UnitPrice,
       UnitPriceDiscount,
       ROUND(LineTotal,2) as LineTotal
  FROM Sales.SalesOrderDetail

Here are the line totals rounded

Using ROUND function on numeric results

Query Results using ROUND

One thing to notice is, though the line total is rounded, the number of decimal places are retained. The round function returns the same data type as the input value, in this case one with 6 decimal places.

Can you think of another way to satisfy this request? What about converting data types?

Check out this query:

SQL
SELECT SalesOrderID,
       SalesOrderDetailID,
       OrderQty,
       UnitPrice,
       UnitPriceDiscount,
       Cast(LineTotal as Decimal(10,2)) as LineTotal
  FROM Sales.SalesOrderDetail

This displays the same results, but since the datatype changed, only two decimal places are displayed.

Signs

ABS

There are many situations where you need to obtain the absolute difference between two values. In these situations, returning the absolute value is useful. The absolute value of a number is the number with the sign removed. The absolute value of -6 is 6.

In t-SQL, you can use the ABS function to do this.

Suppose the sales manager would like to understand which products differ the most from the industry accepted price of $520? To get the absolute difference, we can use t-SQL.

SQL
SELECT    ListPrice,
          ABS(ListPrice - 520) AbsoluteDifference
  FROM    production.Product
 WHERE    ListPrice > 0
 ORDER BY AbsoluteDifference

Notice how we also sort on the AbsoluteDifference. This allows us to sort by the magnitude of the difference and not whether it is a negative or positive value.

SIGN

Speaking of negative and positive values, one handy function I’ve found to determine a value’s sign is to use the SIGN function. This function returns a -1, 0, or 1 depending on whether the value being tested is negative, zero, or positive.

Here is an example of it being used.

SQL
SELECT    ListPrice,
          ABS(ListPrice - 520) AbsoluteDifference,
          SIGN(ListPrice - 520) DifferenceSignFunction,
          CASE
             WHEN SIGN(ListPrice - 520) < 0 THEN -1
             WHEN SIGN(ListPrice - 520) = 0 THEN 0
             WHEN SIGN(ListPrice - 520) > 0 THEN 1
          END DifferenceSignCase
  FROM    production.Product
 WHERE    ListPrice > 0
 ORDER BY AbsoluteDifference

There are couple of things to notice in the results:

Useing SIGN to determine a values sign.

SIGN returns -1, 0, or 1 depending on a value’s sign

First notice that I also show how you can do the same function using the CASE statement. In this instance, SIGN is certainly simpler. You’ll find in SQL, there are usually several ways to obtain a solution!

The second point is notice that the columns AbsoluteDifference and DifferenceSignFunction are the same data type as ListPrice. In general, arithmetic functions such as ABS return the same datatype as their inputs. Other function, such as the trigonometric functions SIN and COS return floats.

The post Introduction to SQL Server’s Mathematical Functions appeared first on Essential SQL.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --