[GIS] PostGIS dissolve geometries from shapefiles

Question

I have a municipal polygon shapefile of 380 municipal records with receptor(schools, hospitals etc..) sums for each municipality. I am trying to use Postgres/PostGIS to dissolve this municipal polygon shapefile layer to county polygon shapefile that contains the sums of the receptors by county

I know I can use the ArcGIS dissolve tool but i want to learn it in postgres

I know how to do the qry where it just returns the records summed but i do not know how to include the geometry for the new county polygon shapefile.

select distinct county as county, sum(schools) as schools,
sum(childcare), sum(respopint) as rez, etc... into countyreport
from munsumreport
group by county;

when I include the geom field, it just returns the the municipal amount of records. when I do sum(geom) as geom in the select and include in the groupby clause it gives me an error – do i have to sum the shape area? not sure what to do

Answer 1

You want ST_Union for your geometry instead of sum:

select distinct ST_Union(geom) as geom, county as county, sum(schools) as schools,
sum(childcare), sum(respopint) as rez, etc... into countyreport
from munsumreport
group by county;