GROUP BY - are JOINS the problem or is it Me?

Below is an SQL statement where I am trying to use a GROUP BY option. If I remove the GROUP BY it works fine but with the GROUP BY included it generates the following error detail: Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'GROUP'. I have tried removing the ORDER BY and it still fails. Is the GROUP BY operation limited by table JOINS or am I missing something really simple?

I am working with three tables: 1) [Stores] has the franchise type (S_Type - kiosk, mall store, free standing, etc) 2) [StoreInfo] has zip code and location info and 3) ZipLatLong has ZipCodes and their respective latitude and longitude. I want to creat a Distinct List of Stores.S_Type but in the result set I have several Stores.S_Types with different Zips thus the rows are not unique. I was expecting the GROUP BY statement to allow me to return a collection of unique Stores.S_Type. The Lat/Long WHERE segment is providing a geographical frame for the result set. Everything is working except for the GROUP BY.

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:localStores1ConnectionString %>"SelectCommand="SELECT Stores.S_Type, StoreInfo.S_Zip, ZipLatLong.ZIPCode, ZipLatLong.Lat, ZipLatLong.Long FROM

StoreInfo INNER JOIN ZipLatLong ON StoreInfo.S_Zip = ZipLatLong.ZIPCode INNER JOIN Stores

ON StoreInfo.S_ID = Stores.S_ID WHERE (((ZipLatLong.Lat)< @.NLat AND (ZipLatLong.Lat)> @.SLat )) AND(((ZipLatLong.Long)< @.ELong AND

(ZipLatLong.Long)> @.WLong )) ORDER BY Stores.S_Type GROUP BY Stores.S_Type;">

The Group BY without an aggregate function is just a standard Distinct so SQL Server is telling you code is not correct, check the docs below and see adjustments you need and the second link download the code samples it will help you. One more thing I see you are doing calculations of Longitude and Latitude without a lot of Math functions that could be a problem, I would look for existing code for such complex calculations. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177673.aspx
http://mhprofessional.com/product.php?isbn=0072260939&cat=&pro

|||

I am using SQL 2000, is there any difference in the GROUP BY statements requirments for SQL 2000 vs. SQL 2005? I obviously do not yet understand the aggregate aspect of the coding and will read your links to hopefully gain the concept.

( The geographical mathematical work is done in its own class and the limits are passed into the SQL statement - without the GROUP BY statement everything works fine but I get multiple Store Types because they exist in different locations.)

|||

I don't think the GROUP BY clause changed check the link below for some advice about GROUP BY. Hope this helps.

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx