From: | bricklen <bricklen(at)gmail(dot)com> |
---|---|
To: | Andreas Forø Tollefsen <andreasft(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ARRAY_AGG and COUNT |
Date: | 2011-02-17 19:21:47 |
Message-ID: | AANLkTi=oicWhxg7RO=icOhnp4=CTGBAoTq053HdS=Zn2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, Feb 17, 2011 at 6:20 AM, Andreas Forø Tollefsen
<andreasft(at)gmail(dot)com> wrote:
> Hi all!
>
> I have tried the below query, but i cannot find a way to select only
> DISTINCT into the ARRAY_AGG function. I also do not want 0 to be included in
> the array or 0 to be counted.
>
> Code:
>
> SELECT priogrid_land.gid, priogrid_land.cell,
> array_to_string(array_agg(g1id), ';') AS g1list,
> array_to_string(array_agg(g2id), ';') AS g2list,
> array_to_string(array_agg(g3id), ';') AS g3list,
> count(distinct g1id) AS g1count, count(distinct g2id) AS g2count,
> count(distinct g3id) AS g3count
> INTO greg_list
> FROM "GREG", priogrid_land WHERE ST_Intersects("GREG".the_geom,
> priogrid_land.cell)
> GROUP BY priogrid_land.gid, priogrid_land.cell;
>
>
> As you see i.e. in g1list 482 is counted twice in row 2. 0 is also counted.
> The distinct works for count, but not for array_agg.
>
I don't have a version earlier than 8.4, but in 8.4+ you can use
DISTINCT in array_agg().
Eg.
array_to_string(array_agg(distinct g1id),';') as ...
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Forø Tollefsen | 2011-02-17 20:11:37 | Re: ARRAY_AGG and COUNT |
Previous Message | Igor Neyman | 2011-02-17 16:37:30 | Re: After insert trigger and select |