Re: Use of array_agg and array string on inner query

The original table is :

c1 c2 c3
1 10
2 20 10
3 20 10

So c3 of row 3 and row 2 are equal to c2 of row 1.

The output I am looking for is :
c1 | array_to_string
----+-----------------
1 | 2,3
2 |
3 |
(3 rows)

How Can I modify this query :

SELECT c1, c2,
ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1
as t2 ON t3.c3 = t2.c2)), ',')
FROM s.t1 t1
GROUP BY c1;
DROP SCHEMA s CASCADE;

to get me the output desired.

Thanks
Shankha Banerjee

On Wed, May 18, 2016 at 1:57 PM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com> wrote:
>
>
> On Thu, May 19, 2016 at 1:09 AM shankha <shankhabanerjee(at)gmail(dot)com> wrote:
>>
>> I have the following piece of code:
>>
>> DROP SCHEMA IF EXISTS s CASCADE;
>> CREATE SCHEMA s;
>>
>> CREATE TABLE "s"."t1"
>> (
>> "c1" BigSerial PRIMARY KEY,
>> "c2" BigInt NOT NULL,
>> "c3" BigInt
>> )
>> WITH (OIDS=FALSE);
>>
>> INSERT INTO s.t1 (c2) VALUES (10);
>> INSERT INTO s.t1 (c2, c3) VALUES (20, 10);
>> INSERT INTO s.t1 (c2, c3) VALUES (30, 10);
>>
>> /* 1. */ SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 =
>> t2.c2;
>>
>> /* 2. */ SELECT t1.c1, ARRAY_TO_STRING(ARRAY_AGG((t2.c1)), ',')
>> FROM s.t1 LEFT JOIN s.t1 as t2
>> ON t2.c3 = t1.c2 GROUP BY t1.c1;
>>
>> /* 3. */ SELECT c1, c2,
>> ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1
>> as t2 ON t3.c3 = t2.c2)), ',')
>> FROM s.t1 t1
>> GROUP BY c1;
>> DROP SCHEMA s CASCADE;
>
>
> The query
>
> SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces
> multiple rows. Since you are calling the aggregate function on the result
> set and not as part of the expression, you are not able to get single row as
> an output.
>
>
>>
>> The output for 1 query:
>>
>> c1
>> ----
>> 2
>> 3
>> (2 rows)
>>
>> 2 Query:
>>
>> c1 | array_to_string
>> ----+-----------------
>> 1 | 2,3
>> 2 |
>> 3 |
>> (3 rows)
>>
>> 3 Query gives me a error:
>>
>> psql:/tmp/aggregate.sql:24: ERROR: more than one row returned
>> by a subquery used as an expression
>>
>>
>> The 3 query uses 1 query as inner query. Is there a way to make Query
>> 3 work with inner query as 1 rather than reverting to 2.
>>
>> 3 output should be same as 2.
>>
>> I understand that the error message says query 1 when used as sub
>> query of 3 cannot return more than one row.
>>
>> Pardon my limited knowledge of database.
>>
>>
>> I have tried out:
>>
>> SELECT c1, c2,
>> ARRAY_TO_STRING((SELECT ARRAY_AGG(t3.c1) FROM s.t1 as t3 JOIN s.t1
>> as t2 ON t3.c3 = t2.c2), ',')
>> FROM s.t1 t1
>> GROUP BY c1;
>>
>
> This would work since the aggregate function has been used on the column.
>
>>
>> Output is :
>>
>> c1 | c2 | array_to_string
>> ----+----+-----------------
>> 2 | 20 | 2,3
>> 1 | 10 | 2,3
>> 3 | 30 | 2,3
>>
>> Could one of you help me with the correct query.
>>
>>
>
> May you should share some more details of exactly what you are expecting and
> what is the output/corelation you want in the result of the query.
>
>>
>> Thanks
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> --
> Best Regards
> Sameer Kumar | DB Solution Architect
> ASHNIK PTE. LTD.
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com