Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to specify a "group by" clause for UpdateCursor arcpy.da. From here I found out that it's possible to do using an optional pair of SQL prefix and postfix. But there are no examples and I'm stuck with the syntaxis problem. Can, please, smbd help me?

PS. I've tried various modifications of "(None, 'GROUP BY "INPUT_FID"')", but still can't get it.

Thank you in advance.

Here is the part of the code I'm using:

oDSFields = ('INPUT_FID','NEAR_FID','DISTANCE', popTotalField, popBlacksField)

kernel = 0
alpha = 0.005
#, "(None, 'GROUP BY "INPUT_FID"')"
with arcpy.da.UpdateCursor("outDistanceSorted", oDSFields, "(None, 'GROUP BY "INPUT_FID"')") as cursor:

    for row in cursor:

        kernel = float(math.exp(-0.5*math.pow(row[2]/alpha, 2)))
share|improve this question
It looks like you are passing your group by as the where part. Try this: arcpy.da.UpdateCursor("outDistanceSorted", oDSFields,sql_clause="(None, 'GROUP BY "INPUT_FID"')" – Nathan W Aug 4 '13 at 22:35
for anyone looking for an answer: the way to do this is by including the objectID in the sql clause even though you don't want to show it, it won't work otherwise... sql = [None,"Group BY r_Identity, ObjectID"] see:… – jonatr Apr 13 '14 at 9:46

As @NathanW pointed out, you are trying to pass your sql_clause to the where_clause parameter. I've found two posts (here and here) that show passing the sql_clause parameter as a tuple (the help also agrees), not a string, so you might want to try the following:

sql = (None, 'GROUP BY "INPUT_FID"')
with arcpy.da.UpdateCursor("outDistanceSorted", oDSFields, sql_clause=sql) as cursor:

If that doesn't work, your syntax should include some escape characters if you want quotations around INPUT_FID (these are a few ways you can do it):

"(None, 'GROUP BY \"INPUT_FID\"')"


"""(None, 'GROUP BY "INPUT_FID"')"""
share|improve this answer
Thank you, guys, for your help. I figured that I was passing GROUP BY statement to the wrong parameter. Such a silly mistake. Although now it says: RuntimeError: An invalid SQL statement was used. [SELECT INPUT_FID, NEAR_FID, DISTANCE, VD01, VD06, LOC_J, LOC_BLACKS_J, OBJECTID FROM outDistanceSorted GROUP BY INPUT_FID] I used to work with SQL before and can't see why this statement doesn't work. I tried different specifications as @Paul said, but nothing works. I also tried to enclose INPUT_FID in quotations a separate string and include OBJECTID field. – ievgenii Aug 5 '13 at 1:16
Actually, I need to make something like this: "SELECT INPUT_FID, SUM(LOC_J) FROM outDistanceSorted GROUP BY INPUT_FID". Is there the way I can do this? Summary statistics (analysis toolbox) doesn't group fields and sql_clause in da.UpdateCursor doesn't allow to use the SUM() function. – ievgenii Aug 5 '13 at 1:37
@EugeneKudko, I'd try to get a simple sql_clause working before implementing other stuff. – Paul Aug 5 '13 at 1:46
I have no clue why the statement doesn't work. I checked the field names and they are correct. The GROUP BY statement works if I specify only one filed "OBJECTID" in the list of fields to select from, but when I include others it gives me an error. – ievgenii Aug 5 '13 at 23:59
up vote 1 down vote accepted

Solved the problem with a much easier way. ArcGIS has a perfect tool "Statistics" in the "Analysis" toolbox, which is very flexible and was used here as the substitution for SQL

...SUM(field1)...GROUP BY field2.

share|improve this answer
But why were the earlier suggestions not working? I'm stuck doing those right now. – Salman Jun 14 '15 at 10:51
@Salman I wasn't able to figure it out, even though I spent a lot of time on it. I also figured ArcGIS has a great number of tools available and usually you don't even need to come up with some sophisticated SQL queries, but just need to find a right tool. – ievgenii Jun 19 '15 at 17:09
Okay but just for the info I got my sql clause perfectly working by writing it like this - sql_clause=(None, 'ORDER BY "GEN" ASC') – Salman Jun 21 '15 at 20:25

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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