More math functions

1. More math functions

When working with numeric data, there are several transformations you may want to apply, such as taking the absolute value, square, square root or even applying a log transformation to provide better results. These sorts of data adjustments are very easy to do with T-SQL and will be the focus of this lesson.

2. Absolute value

There are times that you do not care if the value is positive or negative, you are only interested in the value of the number. When that is the case, you can use ABS to return only non-negative values.

3. Using ABS in T-SQL (I)

Math functions such as the ones introduced in this lesson are different from aggregations. These functions are applied on every item in a column, and nothing is summarized.

4. Using ABS in T-SQL (II)

In this example we return the absolute value of the DurationSeconds column. Note the values in the DurationSeconds column once the absolute value has been applied are always positive.

5. Squares and square roots in T-SQL

To find the square root of a number, you can use the SQRT function, and to find the square of a number, you can use the SQUARE function as shown here.

6. Logs

Transforming numeric columns logarithmically is common practice. For example, if you want to analyze price and the data appears skewed, you might want to take the logarithmic value to move the skew to a more normalized bell curve. You can use the LOG function for this purpose. By default, it returns the natural logarithm.

7. Calculating logs in T-SQL

Here we show how to calculate log base 10 of the DurationSeconds column. The first argument to LOG is the column name and the second argument is the base 10.

8. Log of 0

Since log 0 is undefined, you will get an error if you try to take the log of 0. So make sure you are applying LOG to only non-zero values in your column.

9. Let's practice!

Now it's your turn.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.