Thread: Using values in an array in a subquery

Hi,

Can anyone give some pointers on how to construct a query to do the
following:

I have a table like this:

create temp table array_test (node integer, members integer[], var
numeric);
insert into array_test values (1, NULL, 1);
insert into array_test values (2, NULL, 2);
insert into array_test values (3, NULL, 3);
insert into array_test values (4, NULL, 4);
insert into array_test values (-3, '{1,2}', 5);
insert into array_test values (-2, '{-3,3}', 6);
insert into array_test values (-1, '{-2,4}',7);


test=# select * from array_test;
node | members | var
------+---------+-----
     1 |         |   1
     2 |         |   2
     3 |         |   3
     4 |         |   4
    -3 | {1,2}   |   5
    -2 | {-3,3}  |   6
    -1 | {-2,4}  |   7
(7 rows)

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

I've been trying with syntax as below but with no success.

select node, sum(var)
     from array_test a1
     where node = any(
         cast((select members
             from array_test a2
             where a1.node = a2.node)
             as integer[]))
     group by node;



Any help would be much appreciated,
David

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

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
>