topic Re: Use GROUP BY in SelectLayerByAttribute where_clause ?? in ArcObjects SDK Questions

Use GROUP BY in SelectLayerByAttribute where_clause ??

Santosh_Pavan_KumarNukala — Fri, 23 Aug 2013 06:53:37 GMT

Hi,
I am developing a stand alone C# Map control application using ArcGIS Engine 10.1. I am trying to select features in a point feature class using "SelectLayerByAttribute" class which requires me to use to use GROUP BY keyword.. The Query goes something like this..

SELECT LONGITUDE, LATITUDE, COUNT(LONGITUDE) FROM LAYER123 GROUP BY LONGITUDE,LATITUDE, COUNT(LONGITUDE) HAVING COUNT(LONGITUDE) > 5;

The idea is to select all the features that have been plotted more than 5 times for a given longitude and latitude. My problem here is that I am unable to execute the above query in SelectLayerByAttribute.where_clause, is there a way that i can execute GROUP BY statements to select features in a given layer. Also let me know if there is a workaround for this..

Thanks and Regards,
Santosh Pavan Kumar N

Re: Use GROUP BY in SelectLayerByAttribute where_clause ??

Santosh_Pavan_KumarNukala — Mon, 26 Aug 2013 05:19:34 GMT

Hi,
I am developing a stand alone C# Map control application using ArcGIS Engine 10.1. I am trying to select features in a point feature class using "SelectLayerByAttribute" class which requires me to use to use GROUP BY keyword.. The Query goes something like this..

SELECT LONGITUDE, LATITUDE, COUNT(LONGITUDE) FROM LAYER123 GROUP BY LONGITUDE,LATITUDE HAVING COUNT(LONGITUDE) > 5;

The idea is to select all the features that have been plotted more than 5 times for a given longitude and latitude. My problem here is that I am unable to execute the above query in SelectLayerByAttribute.where_clause, is there a way that i can execute GROUP BY statements to select features in a given layer. Also let me know if there is a workaround for this..

Thanks and Regards,
Santosh Pavan Kumar N

Can someone throw some light on this please.. need a solution or a workaround.

Re: Use GROUP BY in SelectLayerByAttribute where_clause ??

Santosh_Pavan_KumarNukala — Mon, 26 Aug 2013 09:12:29 GMT

A similar post was left unanswered http://forums.arcgis.com/threads/29621-Selection-QUERY-duplicits?highlight=GROUP+BY , I had tried to do a workaround as mentioned in the link by using WHERE .. IN ( Select .. GROUP BY .. HAVING ..) but the problem I need to perform GROUP BY on multiple fields and need to Select Longitude and Latitude in Subquery, hence i cannot use "WHERE Longitude, Latitude IN" as you can use only one field while using IN.

Request any kind of help.

Regards,
Pavan

Re: Use GROUP BY in SelectLayerByAttribute where_clause ??

RichardFairhurst — Mon, 26 Aug 2013 13:35:21 GMT

A similar post was left unanswered http://forums.arcgis.com/threads/29621-Selection-QUERY-duplicits?highlight=GROUP+BY , I had tried to do a workaround as mentioned in the link by using WHERE .. IN ( Select .. GROUP BY .. HAVING ..) but the problem I need to perform GROUP BY on multiple fields and need to Select Longitude and Latitude in Subquery, hence i cannot use "WHERE Longitude, Latitude IN" as you can use only one field while using IN.

Request any kind of help.

Regards,
Pavan

Unless you use a Personal Geodatabase (Access) or an SDE database like SQL Server or Oracle, GROUP BY can only return one value and cannot be correlated to any other fields in a table (I.e., the max is for every record in the entire table). A file geodatabase or shapefile do not support the subquery syntax you want to use. For those types of databases you must use the Summary Statistics tool and a join to do the selection based on the joined grouped summary values. A multi-field join would have to use the Make Query Table tool.

Generally I breakdown and concatenate the multiple fields and use the Summary Statistics to do the selection or data transfer through a standard join.

Re: Use GROUP BY in SelectLayerByAttribute where_clause ??

Santosh_Pavan_KumarNukala — Tue, 27 Aug 2013 09:24:32 GMT

Unless you use a Personal Geodatabase (Access) or an SDE database like SQL Server or Oracle, GROUP BY can only return one value and cannot be correlated to any other fields in a table (I.e., the max is for every record in the entire table). A file geodatabase or shapefile do not support the subquery syntax you want to use. For those types of databases you must use the Summary Statistics tool and a join to do the selection based on the joined grouped summary values. A multi-field join would have to use the Make Query Table tool.

Generally I breakdown and concatenate the multiple fields and use the Summary Statistics to do the selection or data transfer through a standard join.

I am using a Personal Geodatabase and i am not able to make out how i can return more than one value as of now, in the query. request help to modify my existing query in such a way that it suits where_clause of SelectLayerbyAttributes. As a beginner in arcobjects I honestly do not know what Summary statistics is.

Regards,
Pavan

Re: Use GROUP BY in SelectLayerByAttribute where_clause ??

RichardFairhurst — Tue, 27 Aug 2013 13:08:58 GMT

I am using a Personal Geodatabase and i am not able to make out how i can return more than one value as of now, in the query. request help to modify my existing query in such a way that it suits where_clause of SelectLayerbyAttributes. As a beginner in arcobjects I honestly do not know what Summary statistics is.

Regards,
Pavan

Summary Statistics is a geoprocessing tool, not ArcObjects per se, although it can be accessed through ArcObjects. Since it is a personal geodatabase, build the query in Access and look at the sql of the subqueries. If you can make it work there it should translate to the query. At least that is how I would approach it.