I have a feature class and a stand alone table, that can be related one to many. I am trying to create a spatial view by joining the two, and applying a count aggregate function. I keep getting errors having to to with the Geometry field in the feature class table, not working with the group by clause. Here is an example.
SELECT FC.MAXIMOID_PF,FC.Shape,FC.OBJECTID,COUNT(TBL.MAXIMOID),TBL.DATE_
FROM trCenterline as FC
LEFT OUTER JOIN trStreetSweeps as TBL
ON FC.MAXIMOID_PF = TBL.MAXIMOID
GROUP BY FC.OBJECTID,FC.MAXIMOID_PF,FC.Shape,FC.ObjectID,TBL.MAXIMOID,TBL.DATE_
Here is the error....
I am running ArcMap 10.3 and I am trying to query an SDE database.
It isn't working because SQL Server Geometry or Geography types cannot be used in a GROUP BY clause. What version of SQL Server are you using? If 2012 or higher, you can use Static Aggregate Geometry Methods to incorporate "grouped" spatial data into query results.
Before directly creating Views using ArcGIS Desktop, try creating the view at the Database end, make sure it gives the desired result there...then create it using ArcGIS Desktop if needed.