Table out_statistics query doesn't work using ArcGIS API for Python

MichaelJimenez

Hi, I'm using ArcGIS API for Python for querying a table using the following parameters:

query = maintenance.query(where='1=1', out_fields='structureid,last_maintenace', return_geometry=False, group_by_fields_for_statistics='structureid', out_statistics='[{"statisticType": "max","onStatisticField": "maintanancedate", "outStatisticFieldName": "last_maintenace"}]')

I'm always getting a error when trying to use the out_statistics parameter and there is no documentation for the syntax for this parameters. I'm assuming that it's the same syntax as the REST API where it work.

Help please!

Here is the complete code:

from arcgis import *
gis = GIS("http://...", "user", "password")

item = gis.content.get('123...')
maintenance = item.tables[0]
deployment = item.layers[0]

query = maintenance.query(where='1=1', out_fields='structureid,last_maintenace', return_geometry=False, group_by_fields_for_statistics='structureid', out_statistics='[{"statisticType": "max","onStatisticField": "maintanancedate", "outStatisticFieldName": "last_maintenace"}]')

RussellBrennan

@MichaelJimenez you need to escape the quotes. I came across this looking for answers when I ran into the same issue so I assume you have moved on. Since the outstatistics parameter is JSON not just a simple string you need to escape the quotes in the outstatistics JSON that is passed in.

This is the code I wrote for my case. 

# Set variables before execution
lName = "TestLayer" #layer name from contents pane
statField = 'TextField255' #field to get statistics from
whereClause = '1=1' #optional where clause

import arcpy
from arcgis.gis import GIS
from arcgis.features import FeatureLayer
gis = GIS("home")

url = arcpy.Describe(lName).catalogPath

layer = FeatureLayer(url)

hClause = "Count({}) > 1".format(statField)

queryResult = layer.query(where=whereClause, groupByFieldsForStatistics=statField, havingClause=hClause, outStatistics="[{\"statisticType\":\"count\",\"onStatisticField\":\"%s\",\"outStatisticFieldName\":\"NonUniqueCount\"}]" % (statField))

queryResult.sdf[['TextField255', 'NonUniqueCount']]

Note that I was using this inside of Pro so I have a mix of arcpy and arcgis api for python in my example.

Hope this helps you or someone else reading this later.