Finding standard deviation using basic math functions

Question

I am trying to get the standard deviation from a table containing income values, using the basic math functions below in postgresql.

This is what I tried:

SELECT sqrt(sum(power(income - (sum(income) / count(income)), 2)) / (count(*) - 1)) FROM income_data

however, I keep getting the following error:

ERROR: aggregate function calls cannot be nested

Has anyone run into this issue? I feel like the logic for obtaining the standard deviation should work, although haven't had any luck thus far, I appreciate any suggestions on how to resolve.

yes, I am aware stddev makes this easier, however, the idea is to use the basic math functions as the ones listed above and accomplish the same result as when using stddev — AnchovyLegend
– AnchovyLegend, Commented Jul 10, 2017 at 22:42

klin · Accepted Answer · 2017-07-10 23:01:07Z

5

You should calculate a mean in a separate query, e.g. in a with statement:

with mean as (
    select sum(income) / count(income) as mean
    from income_data
)
select sqrt(sum(power(income - mean, 2)) / (count(*) - 1)) 
from income_data
cross join mean;

or in a derived table:

select sqrt(sum(power(income - mean, 2)) / (count(*) - 1)) 
from income_data
cross join (
    select sum(income) / count(income) as mean
    from income_data
) s;

answered Jul 10, 2017 at 23:01

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Finding standard deviation using basic math functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related