Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group