Aggregate functions across multiple columns in postgres

Question

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?

klin · Accepted Answer · 2014-05-11 21:12:56Z

3

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;

answered May 11, 2014 at 21:12

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

sparky Over a year ago

Spot on - I was missing the alias - this would be why I kept getting the syntax error...

Pavel Stehule · Accepted Answer · 2014-05-11 20:17:29Z

2

you can define own aggregates for array variable:

CREATE OR REPLACE FUNCTION public.sum_sfunc(double precision, double precision[])
 RETURNS double precision LANGUAGE sql
AS $function$
  SELECT coalesce($1,0) + sum(v) FROM unnest($2) g(v)
$function$

CREATE AGGREGATE sum(BASETYPE=double precision[], 
                      SFUNC=sum_sfunc, STYPE=double precision);

postgres=# select * from fo;
 a  │ b  
────┼────
 10 │ 20
 30 │ 40
(2 rows)

postgres=# select sum(array[a,b]) from fo;
 sum 
─────
 100
(1 row)

Similar steps you can do with other aggregates, but implementation of AVG is little bit harder, and median is next level. But all is possible, see http://www.postgresql.org/docs/9.3/static/xaggr.html

answered May 11, 2014 at 20:17

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

1 Comment

sparky Over a year ago

Hmm. Thanks for your help but I'm not sure I fancy reimplementing all the aggregate functions! It just seems a little odd that I can't pass the setof which comes from my original query to the existing ones.

Collectives™ on Stack Overflow

Aggregate functions across multiple columns in postgres

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related