Using values in an array in a subquery

From: David Orme <d(dot)orme(at)imperial(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Using values in an array in a subquery
Date: 2005-11-09 17:39:02
Message-ID: 326D1DF0-C8BB-4C85-8132-5B3478B7006A@ic.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Sean Davis 2005-11-09 18:07:36 Re: Dump/restore needed from 8.1b1 to 8.1.0?
Previous Message Gavin Henry 2005-11-09 16:44:02 Exception: Bad Timestamp Format at 0 in 'now'