On Thu, 19 May 2016, 2:07 a.m. shankha, <shankhabaner...@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.ku...@ashnik.com> > wrote: > > > > > > On Thu, May 19, 2016 at 1:09 AM shankha <shankhabaner...@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
