Jump to content

SQL Group By Clause


Pentium
 Share

Recommended Posts

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  
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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] "

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...