Re: Use of array_agg and array string on inner query

On Thu, 19 May 2016, 2:07 a.m. shankha, <shankhabanerjee(at)gmail(dot)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(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
>
>
> --
> 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