Re: Use of array_agg and array string on inner query - Mailing list pgsql-general

I cannot move the array_agg to around the column name. It has to work
as a inner query.

I will try out your other suggestion.
Thanks
Shankha Banerjee


On Wed, May 18, 2016 at 2:26 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
>
>
> On Thu, 19 May 2016, 2:07 a.m. shankha, <shankhabanerjee@gmail.com> wrote:
>>
>> 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;
>
>
> Move array_agg call around the column name instead of calling it on the
> select output.
>
> The 4th query you have used seems to be working except that it 'kind of'
> does a cross product or lateral join. You might want to use a CTE instead if
> bested select and use that with OUTER JOIN or may be in the inner query use
> a correlated where clause (where t1.c2=t2.c2)
>
>>
>> to get me the output desired.
>>
>> Thanks
>> Shankha Banerjee
>>
>>
>> On Wed, May 18, 2016 at 1:57 PM, Sameer Kumar <sameer.kumar@ashnik.com>
>> wrote:
>> >
>> >
>> > On Thu, May 19, 2016 at 1:09 AM shankha <shankhabanerjee@gmail.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@postgresql.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
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.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