Thread: ARRAY_AGG and COUNT
<span class="Apple-style-span" style="font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif;font-size: 13px; -webkit-border-horizontal-spacing: 1px; -webkit-border-vertical-spacing: 1px; ">Hi all!<br /><br/>I am working on a query to identify which group ids exists within a spatial cell. In this case i have the GREG tablewhich has polygon data and the priogrid_land which have the cell polygon.<br />I want to identify which and how manyGREG group ids exists within each of the priogrid_land cells.<br /><br />I have tried the below query, but i cannot finda 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 becounted.<br /><br />Any suggestions on how to accomplish the above?<br /> Thanks!<br /><br /><div style="margin-right:20px; margin-bottom: 20px; margin-left: 20px; margin-top: 5px; "><div class="smallfont" style="font:normal normal normal 11px/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; margin-bottom:2px; "> Code:</div><pre class="alt2" style="background-color: rgb(244, 244, 244); color: rgb(0, 0, 0); font-weight:normal; font-style: normal; font-variant: normal; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left:0px; padding-top: 6px; padding-right: 6px; padding-bottom: 6px; padding-left: 6px; border-top-width: 1px; border-right-width:1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: inset; border-right-style: inset;border-bottom-style: inset; border-left-style: inset; border-color: initial; width: 640px; height: 98px; overflow-x:auto; overflow-y: auto; "> <span class="highlight" style="color: rgb(255, 0, 0); font-weight: bold; ">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;</span></pre></div><br /><br />This will give this result:<br />g1list; g2list;g3list; g1count; g2count; g3count<br />"482";"0";"0";1;1;1<br />"23;482;482";"0;0;0";"0;0;0";2;1;1<br /> "1227;482;23";"0;0;0";"0;0;0";3;1;1<br/>"1227;23;482;66";"0;0;0;0";"0;0;0;0";4;1;1<br />"1227;23;66";"0;0;0";"0;0;0";3;1;1<br/><br />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.<br /><br />Cheers.</span>
On Thu, Feb 17, 2011 at 6:20 AM, Andreas Forø Tollefsen <andreasft@gmail.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 ...
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@gmail.com>
On Thu, Feb 17, 2011 at 6:20 AM, Andreas Forø Tollefsen
<andreasft@gmail.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.I don't have a version earlier than 8.4, but in 8.4+ you can use
> The distinct works for count, but not for array_agg.
>
DISTINCT in array_agg().
Eg.
array_to_string(array_agg(distinct g1id),';') as ...
On Thu, Feb 17, 2011 at 12:11 PM, Andreas Forø Tollefsen <andreasft@gmail.com> wrote: > 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. You can probably select your values in a subquery and filter out the 0 value results in the WHERE clause, then apply your existing query (with distinct array_agg) to the outer query. Or another way would be to use a CASE statement to skip the 0 values. eg. array_to_string(array_agg(distinct (case when g1id <> 0 then g1id end) ),';') ... The array_to_string transformation should trim out the NULLs from the CASE statement.