In Oracle what is the difference between STDEV and STDEV_SAMP functions

oracleoracle-11g-r2

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?

Best Answer

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:

enter image description here

Population STDDEV:

enter image description here

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

Related Question