Re: ARRAY_AGG and COUNT

Great. Thanks. Do you have a suggestion on how to ignore the group id's with
0 as value?
I dont want these to be counted.

Thanks!

2011/2/17 bricklen <bricklen(at)gmail(dot)com>

> 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 ...
>