I have a postgresql table with multiple fields containing integers (a1,a2,a3 etc).
I want to run aggregate functions(mean, standard deviation etc) across more than one of the columns at once. (Some of them may have a reasonable number of nulls, so I don’t want to just generate column averages and then average those).
I can get a set of integers with
SELECT unnest(array[a1,a2,a3]) as values FROM table
but I then can’t get the aggregate functions to take this as input.
Can anyone give me any hints on how I could get this to work?
With a subquery you have all rows at your disposal:
SELECT sum(val) FROM ( SELECT unnest(array[a1,a2,a3]) as val FROM table) alias;
You can also group your rows, for example:
SELECT field, sum(val) FROM ( SELECT field, unnest(array[a1,a2,a3]) as val FROM table) alias GROUP BY field;