In Oracle what is the difference between STDEV and STDEV_SAMP functions? 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?
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