4
fc = "C:\Users\AuxS3-4\Documents\ArcGIS/Default.gdb\Malla" 
    fields = ('OBJECTID','Sentido','Carriles','idMalla') 
    sqlclause=(None, "GROUP BY Sentido")
cursor = arcpy.da.SearchCursor(fc, fields,sql_clause=sqlclause)

not working

RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Sentido, idMalla FROM Malla GROUP BY Sentido

arcgis 10.1!!!

2
  • 2
    I have tested this and found the same problem at 10.2 with a file geodatabase. I tried with and without enclosing the field name in quotes. The GROUP BY clause is supposed to be supported at 10.1 and up in file geodatabases.
    – blah238
    Oct 2, 2013 at 23:52
  • 2
    Looks like another thread on this topic. I was testing locally and also had trouble: gis.stackexchange.com/questions/67810/…
    – bcollins
    Oct 3, 2013 at 4:30

1 Answer 1

2

First, you can't include more fields than those that are being grouped unless the other fields are being used in an aggregation (Sum, Count, Max, Min, etc.). This isn't a limitation of ArcGIS, but of SQL.

In other words, for every value of Sentido, there will be several values for OBJECTID, Carriles and idMalla. How can it group by Sentido and return values for the other fields at the same time?

Secondly, and most important I suppose, this appears to be a bug (in 10.1 at least), or we are all misunderstanding how to use the sql_clause parameter of arcpy.da.SearchCursor. I've tried several combinations of using a GROUP BY clause with a search cursor and nothing will work. It keeps including the ObjectID field even when not specified in the fields parameter, which causes an error due to the above.

So, even when you do get your fields formatted properly, I don't think it will work as expected. I recommend contacting ESRI about this.

3
  • It depends on the implementation whether an aggregation function is required, but from my understanding, it should not be required and should simply return an indeterminate (random, or perhaps first) value for other columns in the grouped rows.
    – blah238
    Oct 3, 2013 at 17:11
  • Although I've never encountered a database that supports the behavior you are describing, it is possible. That would be undefined behavior though, and I would never recommend designing any process, code or script around it. Oct 4, 2013 at 13:48
  • I just know that MySQL works that way, not sure about others.
    – blah238
    Oct 4, 2013 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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