SQL Group By Clause

[Pentium]

Hi guys and girls from having learned a lot from W3Schools I came across a problem which even the site doesn't explain so came to the forums for some guidance. Having searched Google hard I found no luck in resolving this problem. I would like to group my fields together according to an expression. Now this seems easy... yes it does as W3Schools tutorials suggest however I am using ASP and embedding SQL into limiting the data produced via this SQL Statement:

dim sqlCommand		sqlCommand = ""	sqlCommand = sqlCommand + "SELECT [Quantity], [Supplier] "	sqlCommand = sqlCommand + "FROM y2005m05 "	sqlCommand = sqlCommand + "GROUP BY [Quantity] "		objRs.open sqlCommand, objconn,1,3

However after running it I get this:Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC SQL Server Driver]

Column 'y2005m05.Supplier' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause./test_pro2/groupbysupp.asp, line 60[/b]Suggestions and Comments are welcome. Thank youPent  

[pulpfiction]

Hi guys and girls from having learned a lot from W3Schools I came across a problem which even the site doesn't explain so came to the forums for some guidance. Having searched Google hard I found no luck in resolving this problem. I would like to group my fields together according to an expression. Now this seems easy... yes it does as W3Schools tutorials suggest however I am using ASP and embedding SQL into limiting the data produced via this SQL Statement:

dim sqlCommand		sqlCommand = ""	sqlCommand = sqlCommand + "SELECT [B][Quantity], [Supplier][/B] "	sqlCommand = sqlCommand + "FROM y2005m05 "	sqlCommand = sqlCommand + "GROUP BY [Quantity] "		objRs.open sqlCommand, objconn,1,3

However after running it I get this:Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC SQL Server Driver]

Column 'y2005m05.Supplier' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause./test_pro2/groupbysupp.asp, line 60[/b]Suggestions and Comments are welcome. Thank youPent  

[right][post=14498]<{POST_SNAPBACK}>[/post][/right]

The fields that you get in the SELECT statement must be a part of the group by. like this sqlCommand = ""sqlCommand = sqlCommand + "SELECT [b][Quantity], [supplier] [/b]"sqlCommand = sqlCommand + "FROM y2005m05 "sqlCommand = sqlCommand + "GROUP BY [b][Quantity],[supplier][/b] "If you have somthing in the SELECT it should be in the GROUP BY too.HTH

[Pentium]

What is I wanted to display the SUM of the Quantity along with the Customer... I simply tried this code:

dim sqlCommand		sqlCommand = ""	sqlCommand = sqlCommand + "SELECT SUM[Quantity], [supp-product], [Customer]  "	sqlCommand = sqlCommand + "FROM y2005m05 "	sqlCommand = sqlCommand + "WHERE [supp-product]='VIT701380' AND [Customer]='B001K' "	sqlCommand = sqlCommand + "GROUP BY [supp-product], [Customer] "		objRs.open sqlCommand, objconn,1,3

However I get the following error...Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC SQL Server Driver]

Invalid column name 'SUM'./test_pro2/groupbysupp.asp, line 61

[pulpfiction]

Hi,That wont work cos when you use an aggregate function on a field then that field must be included in the GROUP BY.Try this....sqlCommand = ""sqlCommand = sqlCommand + "SELECT SUM[Quantity], [supp-product], [Customer] "sqlCommand = sqlCommand + "FROM y2005m05 "sqlCommand = sqlCommand + "WHERE [supp-product]='VIT701380' AND [Customer]='B001K' "sqlCommand = sqlCommand + "GROUP BY [Quantity],[supp-product], [Customer] "