Re: Using values in an array in a subquery - Mailing list pgsql-novice

On Wed, Nov 09, 2005 at 05:39:02PM +0000, David Orme wrote:
> I want to be able to aggregate a value for each row based on the
> values of var for the rows with node values appearing in the members
> array.  If the aggregate was sum, then I'm looking to get something
> like this:
>
> node | sum
> -----+-----
>    1 |
>    2 |
>    3 |
>    4 |
>   -3 |   3
>   -2 |   8
>   -1 |  10

Is this what you're looking for?  It works for me in 7.4 and later,
at least with your test data.

SELECT a.node, sum(b.var)
FROM array_test AS a
LEFT OUTER JOIN array_test AS b ON b.node = ANY(a.members)
GROUP BY a.node;

 node | sum
------+-----
   -1 |  10
   -2 |   8
   -3 |   3
    4 |
    3 |
    2 |
    1 |
(7 rows)

--
Michael Fuhr