3

Imagine I have a table like:

games (
    scores INTEGER[]
)

And it had values

{1,2}
{3,4}
{}

I'd like to do arbitrary aggregations on that array.

For example, how would I do SELECT SUM(scores) AS total FROM games? Or an average? Or a max?

I'd like to get out:

3
7 
0

I tried SELECT SUM(UNNEST(scores)) AS total FROM games but that gave ERROR: aggregate function calls cannot contain set-returning function calls.

1 Answer 1

4

You need a scalar subquery:

select (select sum(i)
        from unnest(scores) as x(i)) as total
from games;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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