topic How to get the Counts when using Group By in a Search Cursor in ArcGIS API for Python Questions

How to get the Counts when using Group By in a Search Cursor

DougBrowning — Wed, 08 Sep 2021 22:07:48 GMT

I dug through a lot of posts but not getting it. I normally use Summary Stats but cannot in this case. Python 2.7.

Using Group By in SQL looks something like

How do I get the count as a field so I can use it? Most SQL examples have count(field) as field but I can't get that to go and not really sure where to put it. I got the trick to add ObjectID.

I want to get a total for each EvaluationID.

Here is what I got (note row[0] is from a upperlevel searchcursor)

selectClause = row[0] + " IS NOT NULL or CAST(" + row[0] + " AS CHARACTER(255)) <> ''" fieldList2 = ["EvaluationID", "ObjectID", "COUNT(EvaluationID) AS idCount"] with arcpy.da.SearchCursor(table, fieldList2, selectClause, sql_clause=(None, 'GROUP BY EvaluationID, ObjectID')) as cursor2: for row2 in cursor2: print "use count here somehow"

I get the error An expected Field was not found or could not be retrieved properly. [idCount]

Thanks a lot

Re: How to get the Counts when using Group By in a Search Cursor

JohannesLindner — Thu, 09 Sep 2021 05:40:07 GMT

Hmm, couldn't get it to work, either.

Possible workarounds:

use the query in the MakeQueryLayer tool and search its output
do it in python:

selectClause = row[0] + " IS NOT NULL or CAST(" + row[0] + " AS CHARACTER(255)) <> ''" fieldList2 = ["EvaluationID", "ObjectID"] tableData = [row for row in arcpy.da.SearchCursor(table, fieldList2, selectClause)] uniqueEvaluationIDs = {td[0] for td in tableData} for evaluationID in uniqueEvaluationIDs: count = len([td for td in tableData if td[0] == evaluationID]) print "count of {}: {}".format(evaluationID, count)

Re: How to get the Counts when using Group By in a Search Cursor

DougBrowning — Wed, 15 Sep 2021 15:05:28 GMT

Thanks I am trying to avoid all the lopping but that was my backup plan.

Seems weird to have Group by in there but no way to actually use it. But I find a lot of the SQL is only partially implemented.

Still hoping someone knows how.