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.

Answer 1

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;