Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL - STDEVP or STDEV and how to use it?

I have a table:

LocationId OriginalValue Mean 1          0.45         3.99   2          0.33         3.99 3          16.74        3.99 4          3.31         3.99 

and so forth...

How would I work out the Standard Deviation using this table and also what would you recommend - STDEVP or STDEV?

like image 502
DtotheG Avatar asked Feb 15 '13 11:02

DtotheG


People also ask

How do you use standard deviation in SQL?

If STDEV is used on all items in a SELECT statement, each value in the result set is included in the calculation. STDEV can be used with numeric columns only. Null values are ignored. STDEV is a deterministic function when used without the OVER and ORDER BY clauses.

How do you find percentiles in SQL?

The PERCENT_RANK function in SQL Server calculates the relative rank SQL Percentile of each row. It always returns values greater than 0, and the highest value is 1. It does not count any NULL values. This function is nondeterministic.

How do you find the standard deviation in access?

StDevP () Function : StDevP() Function in MS Access is used to estimate the standard deviation for a population. The main difference between the StDevP() function and StDev() function is StDevP evaluates a population, and the StDev evaluates a population sample.


1 Answers

To use it, simply:

SELECT STDEVP(OriginalValue) FROM yourTable 

From below, you probably want STDEVP.

From here:

STDEV is used when the group of numbers being evaluated are only a partial sampling of the whole population. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased."

STDEVP is used when the group of numbers being evaluated is complete - it's the entire population of values. In this case, the 1 is NOT subtracted and the denominator for dividing the sum of squared deviations is simply N itself, the number of observations ( a count of items in the data set ). Technically, this is referred to as "biased." Remembering that the P in STDEVP stands for "population" may be helpful. Since the data set is not a mere sample, but constituted of ALL the actual values, this standard deviation function can return a more precise result.

like image 98
Bernhard Barker Avatar answered Sep 22 '22 08:09

Bernhard Barker