In Oracle what is the difference between STDEV and STDEV_SAMP functions?

Question

We have a query that should return NULL when doing standard deviations in groups that have only 1 row. The normal STDDEV function returns zero. However I found another function called STDDEV_SAMP which returns NULL when there's only one row. It seems to return the same result as normal STDDEV in all other respects, and the Oracle documentation (see: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions159.htm) says:

STDDEV...differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns null.

But my question is, is this the ONLY difference?

Answer 1

I would assume STDEV_SAMP is calculating the sample standard deviation and the other the population standard deviation.

See https://math.stackexchange.com/questions/15098/sample-standard-deviation-vs-population-standard-deviation for some discussion of the difference. A quick search brings up https://www.mathsisfun.com/data/standard-deviation-formulas.html which seems to be a more "layman's" explanation.

Of course the functions could be misleadingly named, if the documentation doesn't specifically state what formula each is using you might want to manually calculate for some sets of figures and compare the results to confirm.

Answer 2

In fact you have STDDEV, STDDEV_SAMP and STDDEV_POP.

Sample and population are standard definitions for STDDEV. When calculating a standard deviation one needs to know if the data set is a sample or the entire population.

There is plenty of explanation on the web about this. See for example Standard Deviation which states:

The standard deviation is a measure of the spread of scores within a set of data. Usually, we are interested in the standard deviation of a population. However, as we are often presented with data from a sample only, we can estimate the population standard deviation from a sample standard deviation. These two standard deviations - sample and population standard deviations - are calculated differently. In statistics, we are usually presented with having to calculate sample standard deviations, and so this is what this article will focus on, although the formula for a population standard deviation will also be shown.

If you look at how a sample standard deviation is calculated versus a population standard deviation you can see 1 is subtracted from the population

Sample STDDEV:

Population STDDEV:

Since one is subtracted from the population when calculating a standard deviation you end up with a division by zero, and oracle gave you the option to choose whether you want NULL or zero in that case.

I think there are mostly historic reasons for the illogical naming, you see that in for example Excel's stddev functions too. The stddev is the sample one, and then later on a stddev.p and stddev.s was added. I guess Oracle took the opportunity to make both aliases behave a bit differently