4

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.

  • 2
    what type of geodatabase do you use ? note that .gdb do not support all queries. – radouxju Sep 18 '14 at 20:01
  • 1
    I'm not sure what ArcGIS's limitations are in regards to GROUP BY, but you cannot have a WHERE ... IN specified like that. Must have one column only. See syntax here: w3schools.com/sql/sql_in.asp – janechii Sep 18 '14 at 20:02
  • 2
    Why do you have this tagged with mysql? File geodatabse doesn't have a RDBMS, so even valid ANSI SQL isn't fully supported. – Vince Sep 18 '14 at 20:07
  • how would I find out what type of .gdb I'm using? It just says file geodatabase. Also I tried only one column in where like where avg_price in (SELECT SUM(avg_price) from output_gis_120_Project GROUP BY lat,lng). Is there a different way to achieve what I am trying to do here? – Timnit Gebru Sep 18 '14 at 20:11
  • The .gdb indicates it's a file geodatabase, rather than a personal geodatabase (.mdb) -- personal GDBs have more thorough SQL support – nmpeterson Sep 18 '14 at 21:14
4

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.

  • @TimnitGebru fantastic! – DPSSpatial Sep 18 '14 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.