Spatial view using feature class and table, by joining and applying an aggregate function.

[GeneSipes1]

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. 

[JoshuaBixby]

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. 

[Asrujit_SenGupta]

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.