CODEWITHSUNDEEP
mysql
Melisa
Melisa

Reputation: 39

MySQL: The correct way to calculate standard deviation

I have the weight of different people.

The average is:

select avg(weight) as avg_weight
from table;

But what's the difference between stddev() and std()? Is it possible to get the standard deviation from avg_weight?

Is this the correct way:

select stddev(weight)
from table;  /* -> is here where the function avg(weight) included? */

Or do I need something like this:

select stddev(avg(weight))
from table;  /* (but this does not work) */

Thanks.

Upvotes: 3

Views: 14293

Answers (2)

phsaires
phsaires

Reputation: 2378

You cannot use AVG inside STD, or any other aggregate functions. Each one in a column. 

SELECT
AVG(table.column) AS average_,
STD(table.column) AS std_,
etc....
FROM
table;

Upvotes: 2

leeyuiwah
leeyuiwah

Reputation: 7152

In MySQL, they provide four different functions related to standard deviation. They could have provided only two STDDEV_POP and STDDEV_SAMP (population vs sample S.D.). They provide the other two for compatibility with standard SQL (STD) and Oracle (STDDEV).

Here is the description from MySQL manual:

STD(expr)

Returns the population standard deviation of expr. This is an extension to standard SQL. The standard SQL function STDDEV_POP() can be used instead.

If there are no matching rows, STD() returns NULL.

STDDEV(expr)

Returns the population standard deviation of expr. This function is provided for compatibility with Oracle. The standard SQL function STDDEV_POP() can be used instead.

If there are no matching rows, STDDEV() returns NULL.

STDDEV_POP(expr)

Returns the population standard deviation of expr (the square root of VAR_POP()). You can also use STD() or STDDEV(), which are equivalent but not standard SQL.

If there are no matching rows, STDDEV_POP() returns NULL.

STDDEV_SAMP(expr)

Returns the sample standard deviation of expr (the square root of VAR_SAMP().

If there are no matching rows, STDDEV_SAMP() returns NULL.

Upvotes: 13

Related Questions