stdDev and stdDevP turned practical

Like many of you, I took statistics in school. And, if you’re like me, stDev sounded interesting, but you were not yet able to turn it into a practical use. Here’s how :

 

First, there’s two stdDev functions : stdDev and stdDevP.

If the list of data you’re performing this function on is complete, then you use stdDevP, otherwise, when you only selected a part of the data, use stdDev.

The way it is calculated is this :

First, every number is subtracted from the average (mean) and squared.

5,3,7 average = 5

5 – 5 = (0)² = 0

3 – 5 = (–2)²= 4

7 – 5 = (2)² = 4

Next, an average is taken and square-rooted.

sqrt(8/3) = 1,632 and that’s how much, on average, those three numbers vary from eachother.

So, if a number is higher than the average + stddev, it is statistically significatly higher than the average.

If the number is lower than the average – stddev, it is statistically significantly lower than the average.

So, now you can create charts, alerters etc using those two numbers :

Above Avg+stddev = Green

Between Avg+stddev and Avg-stddev = white

Below Avg-stddev = Orange

Soundex

At the moment, I’m brushing up a bit on my SQL knowledge. It’s paper-thin and building reports in Reporting Services does require a pretty decent bag of tricks.

The latest trick I learned is Soundex.

select firstname, LastName, SOUNDEX(lastname)
from SalesLT.Customer
where SOUNDEX(lastname) = SOUNDEX('Brown')

There you have it, the Soundex function is able to find names in a database that sound the same.

In this case, SQL will come back with names like : Brown, Brian and Bruno.

Nifty.. really nifty