[GIS] Perform a group by query on geodatabase in ArcMap

arcgis-desktoparcmapfile-geodatabaselayerssql

I have a geodatabase called predictions.gdb and a feature class called output_gis_120_Project in that database. I imported this feature class as a layer in ArcMap. The attribute table with columns lat,lng,attrx,attry,avg_price which is in the form

>

  • lat1,lng1,attr1,attr2,avg_price1

  • lat1,lng1,atrr3,attr4,avg_price2

etc… I would like to perform a sql query which is

SELECT lat,lng,SUM(avg_price) from output_gis_120_Project GROUP BY
lat,lng;

I read that ArcGIS can only have group by statements in a subquery so I went to layer—>properties–>definition–>query builder and rewrote my query as

"select * from output_gis_120_Project where lat,lng,sum_price in
(SELECT lat,lng,SUM(avg_price) sum_price from output_gis_120_Project
GROUP BY lat,lng)

I keep on getting an error saying that there was an error with the expression. I tried putting the fields in brackets [] and only selecting avg_price in the subquery, but this didn't help either.

Best Answer

To complete these type of relational database queries using an ArcGIS geodatabase (not a relational database as mentioned above) you will have to use a tool like Summary Statistics

To do the groupings, use the 'case' field (ie. the field you want to group by)

The SUM option Adds the total value for the specified field.

Related Question