current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Is it possible to apply aggregates (like avg(), stddev()) on all the values in an integer[] field (or other arrays of numbers)?

CREATE TABLE widget
(
  measurement integer[]
);

insert into widget (measurement) values ( '{1, 2, 3}');

select avg(measurement::integer[]) from widget;

ERROR:  function avg(integer[]) does not exist
LINE 4: select avg(measurement::integer[]) from widget;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function avg(integer[]) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 71

I can work around by splitting the array into multiple rows like

select avg(m)::float from (select unnest(measurement) m from widget) q;

but it's less elegant.

Thank you.

share|improve this question
    
You can define a custom aggregate, that will work with an array. Or create a simple function that will convert an array to a single aggregated value and aggregate on top of this function. –  Igor Romanchenko Sep 23 '13 at 15:12
add comment

1 Answer

up vote 1 down vote accepted

you can create simple function like this:

create function array_avg(_data anyarray)
returns numeric
as
$$
    select avg(a)
    from unnest(_data) as a
$$ language sql;

and query it like this

select avg(array_avg(measurement))
from widget;

or you can simply do

select avg((select avg(a) from unnest(measurement) as a))
from widget;

sql fiddle demo

share|improve this answer
    
Great, thank you. –  wishihadabettername Sep 23 '13 at 18:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.