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

Question

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

dougbrowning · Answer

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.

JohannesLindner · Answer

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)