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@xxxxxxxxxx> wrote: > > > On Thu, 19 May 2016, 2:07 a.m. shankha, <shankhabanerjee@xxxxxxxxx> 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@xxxxxxxxxx> >> wrote: >> > >> > >> > On Thu, May 19, 2016 at 1:09 AM shankha <shankhabanerjee@xxxxxxxxx> >> > 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@xxxxxxxxxxxxxx) >> >> 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@xxxxxxxxxxxxxx) >> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
