I am trying to write a simple select query in PgSQL but I keep getting an error. I am not sure what I am missing. Any help would be appreciated.
select residuals, residuals/stddev_pop(residuals)
from mySchema.results;
This gives an error
ERROR: column "results.residuals" must appear in the GROUP BY clause or be used in an aggregate function
Residuals is a numeric value (continuous variable)
What am I missing?
stddev_pop is an aggregate function. That means that it takes a set of rows as its input. Your query mentions two values in the SELECT clause:
residuals, this is a value from a single row.stddev_pop(residuals), this is an aggregate value and represents multiple rows.You're not telling PostgreSQL how it should choose the singular residuals value to go with the aggregate standard deviation and so PostgreSQL says that residuals
must appear in the GROUP BY clause or be used in an aggregate function
I'm not sure what you're trying to accomplish so I can't tell you how to fix your query. A naive suggestion would be:
select residuals, residuals/stddev_pop(residuals)
from mySchema.results
group by residuals
but that would leave you computing the standard deviation of groups of identical values and that doesn't seem terribly productive (especially when you're going to use the standard deviation as a divisor).
Perhaps you need to revisit the formula you're trying to compute as well as fixing your SQL.
If you want to compute the standard deviation separately and then divide each residuals by that then you'd want something like this:
select residuals,
residuals/(select stddev_pop(residuals) from mySchema.results)
from mySchema.results
group by residuals doesn't make sense in the statistics since stddev_pop(residuals) would be zero in that case. Maybe you want to compute residuals/stddev as in my updated answer.