Re: Using values in an array in a subquery

On 10 Nov 2005, at 18:15, Michael Fuhr wrote:

> 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;

That's fantastic - exactly what I was looking for.

Many thanks,
David

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