From: | Andreas Forø Tollefsen <andreasft(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | ARRAY_AGG and COUNT |
Date: | 2011-02-17 14:20:10 |
Message-ID: | AANLkTikzK=SVbk8Jzaci_Bb3cnHvVfSBpO0_mO=WSrfN@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi all!
I am working on a query to identify which group ids exists within a spatial
cell. In this case i have the GREG table which has polygon data and the
priogrid_land which have the cell polygon.
I want to identify which and how many GREG group ids exists within each of
the priogrid_land cells.
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.
Any suggestions on how to accomplish the above?
Thanks!
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;
This will give this result:
g1list; g2list; g3list; g1count; g2count; g3count
"482";"0";"0";1;1;1
"23;482;482";"0;0;0";"0;0;0";2;1;1
"1227;482;23";"0;0;0";"0;0;0";3;1;1
"1227;23;482;66";"0;0;0;0";"0;0;0;0";4;1;1
"1227;23;66";"0;0;0";"0;0;0";3;1;1
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.
Cheers.
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2011-02-17 16:37:30 | Re: After insert trigger and select |
Previous Message | Gnanakumar | 2011-02-17 07:05:51 | Is it possible to get DISTINCT rows from RETURNING clause? |