topic Group By functionality of da.cursors sql_clause in Geoprocessing Questions

Group By functionality of da.cursors sql_clause

PaulBrandt — Sun, 17 Feb 2013 10:53:47 GMT

I must be doing something wrong. I am trying to use the Group By functionality of the new da.SearchCursor, but keep getting the error:
RuntimeError: Underlying DBMS error [Microsoft SQL Server Native Client 11.0: Column 'demo.test.PRCL_AL_WEST.OBJECTID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.] [demo.test.PRCL_AL_WEST][STATE_ID = 88181]

I don't understand this as I am not including OBJECTID in my field list, and it wouldn't make sense to include it in my Group By.

The problem I am trying to solve is; I have a bunch of feature classes in different datasets that are identical except that they have different projections (don't ask, I can't change this part), so I get the list of feature classes in the fcs variable by using the da.Walk functionality (really cool), filtered with the fnmatch module, and am trying to get record counts to track progress by date (removing time portion), user and create method code.

I know there are other tools like Statistics that can be used, but I am mostly just experimenting with the new 10.1 functionality, and would like to get this working, as it has lots of potential.

In the code below conn is a connection to a sqlite3 in-memory database and c is the cursor for it, I am inserting the individual feature class results into the sqlite table, then doing an aggregate to get the total counts from there, later in the script.

fieldList = ["COUNT(*) as GroupCount", "CONVERT(DATE, CRET_TS) as CRET_TS", "GISF_CRET_METH_CD", "BWCreator"]
sqlclaus = [None, "GROUP BY CONVERT(DATE, CRET_TS),GISF_CRET_METH_CD,BWCreator"]
#Gather the counts from the individual feature classes
for fc in fcs:
    for row in arcpy.da.SearchCursor(fc, fieldList, sql_clause=sqlclaus):
        #insert the individual feature class counts into the sqlite table
        vals = [fc]
        vals.extend(row)
        c.execute("INSERT INTO rslt VALUES(?,?,?,?,?)", vals)
        conn.commit()

Any help you can provide is much appreciated.

Thank you.

Re: Group By functionality of da.cursors sql_clause

ShaunWalbridge — Thu, 06 Jun 2013 19:07:52 GMT

I believe that the call to arcpy.da.searchCursor will always include 'OBJECTID' in the output results, and so you'll need to include that in your aggregation function. So after your main 'GROUP BY' statement, append 'OBJECTID', in your case:

sqlclaus = [None, "GROUP BY CONVERT(DATE, CRET_TS),GISF_CRET_METH_CD,BWCreator,OBJECTID"]

This won't affect the results as the column only contains unique results, but should allow your above query to run (provided this was the sole issue).

Re: Group By functionality of da.cursors sql_clause

WesleyAntal2 — Tue, 10 Jun 2014 17:55:02 GMT

I'm with this problem too, but I can't aggregate the "OBJECTID" in clause sql (group by), because impact in results. You have another solution?

SQL:

select avg(velocity), max(velocity), team, date(date_team) from
table where (date(date_team) between '2013-06-27' and '2013-07-20') and team= 3260 group by team, date(date_team)

Re: Group By functionality of da.cursors sql_clause

ShaunWalbridge — Wed, 11 Jun 2014 04:47:37 GMT

This is a general issue when using SQL aggregate functions, but basically, just include the additional column you're grouping on in the results. So, something like MIN(OBJECTID) in the WHERE clause of the SQL statement.

Re: Group By functionality of da.cursors sql_clause

WesleyAntal2 — Wed, 11 Jun 2014 12:25:27 GMT

I try, but too return error.

I solved with other way. Used the arcpy.ArcSDESQLExecute with my clause SQL (select avg(velocity), max(velocity), team, date(date_team)::varchar(10) from table where (date(date_team) between '27/06/2013' and '20/07/2013') AND team = 3260 group by team, date(date_team)::varchar(10)) and after with "for" inserted in my temporary table.